Copy data from multiple identical sheets to a consolidation sheet by macro

[Mel Smith]

I have a workbook consisting of 9 sheets. The layout of each sheet is identical. Sheet 1 (named Combined report) and sheets 2 to 9 will auto-name based on the employees name
which I shall enter into cell A3 as each new sheet is taken into use.

What I would like to do is to have a macro or command button that will copy the contents of each sheet (sheets 2-9) and paste them onto sheet 1 (Combined report) in sheet order, i.e. starting at
cell A4, copy sheet 2, however many lines that may be, and then sheet 3 and so on until all sheets (2 - 9) and copied and pasted.

The data on each sheet to be copied is within the range A4:L4 but the number of rows is variable. It might be one row or 20 rows.

Any help will be very much appreciated!

Mel

 

[Mel Smith]

Aah, no but i can make it so that it does. Done that and, of course, it works but on the Combined report sheet it on each row, instead of pasting the contents of sheet 2, 3 etc column A (i.e. Smith, Jones) it copies the contents of cell A3 on the Combined report sheet.

Mel

 

[Fluff]

Is there any column that will always have data on every row?

 

[Mel Smith]

No. On each sheet in A3:L3 there is column headers but below that each sheet is blank until i enter data. Does that make sense?

Mel

 

[Fluff]

Does that make sense?

No because that means there is nothing to copy.
Once you have entered data is there any particular column that will always have a value?

 

[Mel Smith]

Yes, on sheets 2 - 9, if I enter data cell B will always have a value.

Mel

 

[Fluff]

In that case, how about

Sub MelSmith()
   Dim Ws As Worksheet, wsCom As Worksheet
   
   Set wsCom = Sheets("Combined Report")
   For Each Ws In Worksheets
      If Ws.Name <> wsCom.Name Then
         Ws.Range("A4:L" & Ws.Range("B" & Rows.Count).End(xlUp).Row).Copy wsCom.Range("A" & Rows.Count).End(xlUp).Offset(1)
      End If
   Next Ws
End Sub

 

[Mel Smith]

Hmmm. I have a command button assigned to the (new) macro MelSmith. When I execute the button, nothing happens... I have removed the two previous sets of code. Sheets 2, 3 and 4 have data in A4:L5, A4:L6 and A4:L7 respectively.

Mel

 

[Fluff]

How about

Sub MelSmith()
   Dim Ws As Worksheet, wsCom As Worksheet
   Dim UsdRws As Long
   
   Set wsCom = Sheets("Combined Report")
   For Each Ws In Worksheets
      If Ws.Name <> wsCom.Name Then
         UsdRws = Ws.Range("B" & Rows.Count).End(xlUp).Row
         If UsdRws > 3 Then Ws.Range("A4:L" & UsdRws).Copy wsCom.Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
      End If
   Next Ws
End Sub

 

[Mel Smith]

Still nothing pasted...

 

[Fluff]

Try stepping through the code using F8, does it ever move to the .Copy part of the code?