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

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

Answer 1

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