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

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
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".
Code:
     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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
How about
Code:
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
 
Upvote 0
How about
Code:
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?
 
Upvote 0
Try
Code:
         Intersect(Ws.Range("A:AE"), Ws.UsedRange[COLOR=#ff0000].Offset(1)[/COLOR]).Copy Sheets("Merged").Range("A" & Rows.Count).End(xlUp).Offset(1)
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top