Copying unknown number of rows from one worksheet to another

[sbecker61]

I have one workbook with 3 worksheets. The 1st sheet is the master and the rest are the "subordinate" spreadsheets. In worksheets 2 thru 3 i have information (cells) about a customer. I may have one line of documentation per sheet, I may have 100 lines of documentation. It varies from week to week. For example,

Sheet 2

AAAAA BBBBB CCCCC DDDDD
EEEEE FFFFF GGGGG HHHHH

Sheet 3

IIIIIIII JJJJJJ KKKKKK MMMMM
NNNN OOOO PPPPPP QQQQQQ
RRRR SSSS TTTTT UUUUUU

I want the master spreadsheet to show:

AAAAA BBBBB CCCCC DDDDD
EEEEE FFFFF GGGGG HHHHH
IIIIIIII JJJJJJ KKKKKK MMMMM
NNNN OOOO PPPPPP QQQQQQ
RRRR SSSS TTTTT UUUUUU

I don't wany any spaces at all between rows. The final result should be a combination of all the sheets (2 thru x) in my workbook. BTW, I may end up having up to 20 tabs / sheets in this workbook eventually. My challenge is (and has been) how do I account for the different numbers of rows in each worksheet and make sure I have one continous / contiguous set of data in my master sheet? Sure, I can copy and paste manually but is there a VBA way to do this? I'm sure the Excel masters here have done this before.

Thanks in advance.

 

[VoG]

Try this: change Master to the name of the master sheet

Sub Cpy()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Master" Then
        ws.UsedRange.Copy Destination:=Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
Next ws
End Sub

 

[sbecker61]

Hi and thank you so much for responding,

My bad. I didn't tell you everything. My info that will be copied always lies in cells a37:g37 to a45:g45. I only want to copy the cells that are not empty to the master spreadsheet.

I know your solution copies everything from the worksheet. I only want to copy those specific cells above.

Please help and thanks again.

Steve

 

[VoG]

Try

Sub Cpy()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Master" Then
        ws.Range("A37:G45").Copy Destination:=Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
Next ws
End Sub

 

[sbecker61]

that worked great. Thank you very much.