Hi,
I have a loop to copy a data range (find last row for data because it always differs) from worksheet 2, paste it in the "Summary" worksheet (next empty row) and delete ws2, then go on to worksheet 3 and repeat.
I'm finding an error on the paste line :" Method 'Range' of Object '_Worksheet' failed.
This is the current code
'Add all tabs to Summary and Delete Tabs
Sub AddToSummary()
Dim lRow As Long
Dim ws As Worksheet
Dim pasteSheet As Worksheet
Set pasteSheet = Worksheets("Summary")
For Each ws In ActiveWorkbook.Worksheets
lRow = pasteSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
If ws.Name <> "Summary" Then
ws.Range("A15:AD" & Range("A" & Rows.Count).End(xlDown).Row).Copy
pasteSheet.Range(lRow).PasteSpecial
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Next ws
Bookmarks