Combining cells from multiple worksheets into rows on single worksheet

[aktheexcelguy]

Hi, I am hoping someone is able to help me. I have a workbook with about 300 tabs. All exactly the same format. I am trying to write a macro that copies all of the data from several cells in each workbook into a single workbook. Let me give more an example.

A1 - First Name
B1 - Last Name
C1 - Phone Number

Result:

Worksheet - Collated
A1 - FIRST NAME
B1 - FIRST NAME
C1 - PHONE NUMBER
NEXT ROW WILL PULL FROM NEXT SHEET
A2 - FIRST NAME
B2 - LAST NAME
C2 - PHONE NUMBER
NEXT ROW WILL PULL FROM THE NEXT SHEET
A3 - FIRST NAME

and so on.

Please someone help explain to me how to do this. I am really stuck.

 

[aktheexcelguy]

I'll put this up and the OP can adjust the range to suit.
But I'm willing to bet there is more to this than we see here, it'll get MUCH bigger...

Sub MM1()
Dim ws As Worksheet, c As Range, dest As Range, lr As Long
lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
For Each ws In Worksheets
    If ws.Name <> "Master" Then
    Set dest = Sheets("Master").Range("A" & lr)
        For Each c In ws.Range("A1, B23, C12")
                dest.Value = c.Value
                Set dest = dest.Offset(0, 1)
        Next c
    End If
lr = lr + 1
Next ws
End Sub

This worked an absolute treat. Thank you very much. I had an assets and liabilites documents for a couple of hundred clients, each of which was a new worksheet. It was incredibly painful. Once I entered in all of the cells into the range above, it generated the results within seconds. Amazing. Thank you for all of your help everybody.

 

[Michael M]

That's probably the best "Guess" I have ever had....