Using VBA, easy way to copy data from a few tabs into 1

[ERed1]

Hi all,

So I have a few tabs with changing names, that sometimes are there and others there aren't. For example. I will always have a "Sheet1" then after that they may not be there, "Sheet1 (2)", "Sheet1 (3)", etc. I need help writing a macro that will copy the data from "Sheet1" into Cell A1, on a sheet called Merge. Then "Sheet1 (2)", will be copied into Merge, except at the bottom of the data from "Sheet1".

     Dim ws As Worksheet

    For Each ws In Worksheets
        If ws.Name Like "*(*)" Then Range("A:AE").Copy Destination:=Worksheets("Merged").Range("A1")
    Next ws


    Sheets("Merged").Select
    With ActiveSheet
        LastRowColumnA = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

I got this to work for copying sheet1, but not the others.

Thanks in advance

 

[Fluff]

How about

Sub ERed1()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      If Ws.Name Like "Sheet1*" Then
         Intersect(Ws.Range("A:AE"), Ws.UsedRange).Copy Sheets("Merged").Range("A" & Rows.Count).End(xlUp).Offset(1)
      End If
   Next Ws
End Sub

 

[ERed1]

How about

Sub ERed1()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      If Ws.Name Like "Sheet1*" Then
         Intersect(Ws.Range("A:AE"), Ws.UsedRange).Copy Sheets("Merged").Range("A" & Rows.Count).End(xlUp).Offset(1)
      End If
   Next Ws
End Sub

This worked great. I have headers on the sheets that are being copied into one. So I have a header half way through my data. Got a quick way of changing that?

 

[Fluff]

Try

         Intersect(Ws.Range("A:AE"), Ws.UsedRange[COLOR=#ff0000].Offset(1)[/COLOR]).Copy Sheets("Merged").Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[ERed1]

That worked perfectly. Thanks for the help again Fluff!

 

[Fluff]

Glad to help & thanks for the feedback