Macro to Compile Data from Different Sheets

[djc]

I have an issue which I think will be fairly easy to solve, but my Google searches have been unfruitful.

I have 50+ sheets with data in table form. The headers in each sheet are consistent and the header range for each tab is A4:AE4. However each sheet will have varying amount of data entered down the rows.

Each that needs the data compiled is name as “J-“ as the first 2 characters. This way I know which sheets I need to pull from.

I have a tab that I call “Master” that will compile all the data.

I need a macro that will find each sheet with the “J-“, find the numberof active rows in that sheet and then copy all the information from the table over in the Master sheet (less the header in row 4).

Once the data is copied over to the Master from the first sheet, it will copy over the data from sheet 2 directly below the information from sheet 1. Once it has found and copied all the data from the “J-“ tabs, it is complete.

Any feedback would be most helpful. Thank you in advance.

 

[Claus Busch]

Hi,

Am Sat, 19 Oct 2013 09:42:31 -0700 (PDT) schrieb djc:

I have an issue which I think will be fairly easy to solve, but my Google searches have been unfruitful.

I have 50+ sheets with data in table form. The headers in each sheet are consistent and the header range for each tab is A4:AE4. However each sheet will have varying amount of data entered down the rows.

Each that needs the data compiled is name as ?J-? as the first 2 characters. This way I know which sheets I need to pull from.

try:

Sub Test()
Dim LRow As Long
Dim wsh As Worksheet

For Each wsh In ThisWorkbook.Worksheets
If Left(wsh.Name, 2) = "J-" Then
With wsh
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A5:AE" & LRow).Copy _
Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
End If
Next
End Sub


Regards
Claus B.

 

[djc]

Works beautifully. Thank you!