Hallo,

benutze das Range-Objekt. Dabei kommt der Laufzeitfehler 1004 vor.

Habe schon einiges ausprobiert. Die zu untersuchende Tabelle ist auch gefüllt.

der Code sieht so aus:

Sub Update_Pipeline_Sales()

Dim i As Variant
Dim a As Integer
Dim b As Integer


Dim Week As String
Dim Year As String
Dim Quartal As String

Week = Format(Now, "ww")

Dim Name As String

Name = "H:\Vertrieb\Sales Force Management\SFM Intern\Forecast\"
Name = Name + FY_Year(Year) + "\"
Name = Name + FY_Year(Year) + "-Q" + Quart_FY(Quartal) + "\"
' später wieder einkommentieren
' Name = Name + "KW" + Week + "\"
Name = Name + "KW17\"
Name = Name + "PipelineSales_080422.xls"

Workbooks.Open Filename:=Name

Windows("Daily_tracking_new.xls").Activate
Sheets("Pipeline_Sales").Select

a = 0
i = 1
Do While a = 0
If Cells(i, 1).Value <> "" Then
i = i + 1
Else
a = 1
End If
Loop

i = i - 1
a = 0
b = 1

Do While a = 0
If Cells(1, b).Value <> "" Then
b = b + 1
Else
a = 1
End If
Loop
b = b - 1


With Sheets("Pipeline_Sales")
.Range(.Cells(1, 1), .Cells(i, b)).ClearContents
End With

a = 0
i = 2
Do While a = 0
If Cells(i, 1).Value <> "" Then
i = i + 1
Else
a = 1
End If
Loop
i = i - 1

a = 0
b = 1
Do While a = 0
If Cells(4, b).Value <> "" Then
b = b + 1
Else
a = 1
End If
Loop
b = b - 1

Range(Cells(2, 1), Cells(i, b)).Select

Selection.Copy
Windows("Daily_tracking_new.xls").Activate
Sheets("Pipeline_Sales").Select
Range("A1").Select
Selection.Insert Shift:=xlToRight
Application.CutCopyMode = False
Windows("PipelineSales_080422.xls").Activate
ActiveWorkbook.Close Savechanges:=False
Application.DisplayAlerts = False

End Sub