Tried crating a Macro to combile multiple worksheets into one. But getting Excel 1004 Error

Question

First attempt at looping in VBA. Tried running a loop to compile multiple worksheets into one worksheet. Getting an error at making the range selection.

 Dim i As Integer

   For i = 1 To Worksheets.Count - 1
   
   Worksheets(i).Select
   Range("a2").Select
   Range(Selection, Selection.End(x1Down)).Select
   Range(Selection, Selection.End(x1ToRight)).Select
   
   Selection.Copy
   
   Worksheets("final").Select
   Range("A1048576").Select
   Selection.End(xlUp).Select
   ActiveCell.Offset(1, 0).Select
   ActiveSheet.Paste

Use Option Explicit. It's `xlDown` and `xlToRight` (**l**, not **1**). And you should urgently read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — FunThomas, Feb 23 '22 at 12:55

score 1 · Answer 1 · edited Feb 23 '22 at 13:34

You have in your formula x1Down and x1ToRight. You need to change the 1 (one) to l (small L). Also do not forget the Next i at the end.

Btw: you can also use lastrow and lastcolumn and copy directly without selecting sheets. LR is set for example on column A to get the last row with data in this column:

Dim LR as long
LR = Sheets("blabla").Cells(Sheets("blabla").Rows.Count, "A").End(xlUp).Row

An example of Copy in one statement with only lastrow:

Sheets("blabla").Range("A12:Z" & LR).Copy Sheets("destination").Range("A1")

You can do the same for lastcolumn

Tried crating a Macro to combile multiple worksheets into one. But getting Excel 1004 Error

1 Answers1