How to combine sheets in Excel

[kamus45]

Hi, I'm new in the forum and I have this question about how to combine sheets in Excel

I got a file with sheets "A1, B1, C1... J1" with data in column A and need to combine it in "Final" sheet, searching in google I find this post:

http://www.mrexcel.com/forum/excel-...e-columns-multiple-sheets-into-one-sheet.html

But don't know how to use it to get what I need.

Sheets("Final").ActivateColumns("A:A").Select
Selection.EntireRow.Delete

Dim ws As Worksheet
For Each ws In Sheets(Array("A1", "B1", "C1", "D1", "E1", "F1", "G1", "H1", "I1", "J1"))
  With ws
    .Range("A:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Copy Sheets("Final").Cells(Rows.Count, "A").End(xlUp).Offset(1)
  End With
Next ws
Sheets("Final").Activate

Thanks for your help

 

[kamus45]

Something weird, i copy the Sheets to a new file and the error stop, but only copy the data of A1 Sheets and ignores the rest, I try recording a macro and the work is done but in a very rustic mode

https://www.mediafire.com/file/tlb43139npagtaw/Macros%20-%20Columnas2.xlsm

Thanks for help me

 

[Peter_SSs]

See how this one goes.

Sub CombineSheets()
  Dim lr As Long
  Dim ws As Worksheet
  
  Sheets("Final").UsedRange.Clear
  For Each ws In Sheets(Array("A1", "B1", "C1", "D1", "E1", "F1", "G1", "H1", "I1", "J1"))
    With ws
      lr = .Columns("A").Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, SearchDirection:=xlPrevious, SearchFormat:=False).Row
      .Range("A1:A" & lr).Copy Destination:=Sheets("Final").Cells(Rows.Count, "A").End(xlUp).Offset(1)
    End With
  Next ws
  Sheets("Final").Activate
End Sub