Использование Select
и Activate
т. Д. Опасно. Вместо этого вы должны явно квалифицировать ваши объекты с их родителями. Ex.
Sheets(1).Range("A1").value = 1
Лучше, чем
Sheets(1).Activate Range("A1").Select Selection.Value = 1
Я немного почистил ваш код:
Option Explicit Public Const DATA = "Data" Public Const DETAILINTERVIEW = "Detailinterview" Public Sub DoMagic() Dim logo As Shape Dim i As Long Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual For i = 1 To Sheets.Count If Sheets(i).Name = DETAILINTERVIEW Then Debug.Print MsgBox("A worksheet " & DETAILINTERVIEW & " exists already!", vbOKOnly) Exit Sub End If Next i Dim ws As Worksheet With ThisWorkbook .Worksheets("Datenblatt_Template").Copy after:=.Worksheets(.Worksheets.Count) Set ws = .Worksheets(.Worksheets.Count) End With With ws .Name = DETAILINTERVIEW .Columns("I:I").ColumnWidth = 1 .Columns("K:K").ColumnWidth = 33 .Columns("M:M").ColumnWidth = 17 .Columns("O:O").ColumnWidth = 3 ActiveWindow.DisplayGridlines = False ActiveWindow.DisplayHeadings = False .Range("A:H").EntireColumn.Hidden = True ThisWorkbook.Worksheets("Templates").Range("T_HEADER").Copy Destination:=.Range("A1") ThisWorkbook.Worksheets("Templates").Range("T_MASTER_HEADER").Copy Destination:=.Range("A2") '*************************** 'I can't get the next line to run because Start is uninitialized '.Range("J2").Value = Range(Start & "!C20") & " - " & Range(Start & "!C21") & " - " & Range(Start & "!C22") '**************************** For Each logo In Sheets(DATA).Shapes If logo.Name = "MY_LOGO" Then logo.Copy .Pictures.Paste .Shapes(1).IncrementLeft 580 .Shapes(1).IncrementTop 4 Exit For End If Next If .Shapes.Count < 1 Then Debug.Print "Logo not found" End With ' Some more Magic End Sub