Need help consolidating data from multiple sheets into one sheet

[gkisystems]

I have a macro that pulls in a tab called "Registration Form" from multiple other files and dumps it into my working file. I also have a "Control Panel" and "Master" tab in my working file. As a result, the tab names in my worksheet are as follows:

Control Panel
Master
Registration Form
Registration Form (2)
Registration Form (3)
...
and so on.

I need all the data in the registration forms to be copied and pasted into the Master tab. On each registration form, the amount of data varies. The largest data range would be A1:F16 with the smallest being A1:F2 on each registration form. The total number of "Registration Form" tabs will also vary, so I am unable to reference the specific tab names.

The following macro does what I need it to except it is static and I need help making it dynamic. Specifically, I need help in telling the macro to go find the "Registration Form" tabs, regardless of however many of them I have instead of looking at specific tab names. Any help is appreciated!

Sub LoadMaster()
'This macro goes to each Registration Form and copies data in columns A-F.
'The data gets appended to the bottom of the data on the Master tab.


    Sheets("Registration Form").Select
    Application.Goto Reference:="R1C1"
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Master").Select
    Application.Goto Reference:="R1C1"
    ActiveSheet.Paste
    
    Sheets("Registration Form (2)").Select
    Application.Goto Reference:="R1C1"
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Master").Select
    Application.Goto Reference:="R1C1"
    Selection.End(xlDown).Select
    Selection.Offset(1, 0).Select
    ActiveSheet.Paste
    
    Sheets("Registration Form (3)").Select
    Application.Goto Reference:="R1C1"
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Master").Select
    Application.Goto Reference:="R1C1"
    Selection.End(xlDown).Select
    Selection.Offset(1, 0).Select
    ActiveSheet.Paste

End Sub

 

[igold]

Would this work for you...

Sub LoadMaster()
'This macro goes to each Registration Form and copies data in columns A-F.
'The data gets appended to the bottom of the data on the Master tab.

    Dim wsct As Single
    Dim i As Single
    wsct = Worksheets.Count
    
    For i = 2 To wsct
    
        Sheets("Registration Form").Select
        Application.Goto Reference:="R1C1"
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        Sheets("Master").Select
        Application.Goto Reference:="R1C1"
        ActiveSheet.Paste
    
        Sheets("Registration Form (" & i & ")").Select
        Application.Goto Reference:="R1C1"
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Master").Select
        Application.Goto Reference:="R1C1"
        Selection.End(xlDown).Select
        Selection.Offset(1, 0).Select
        ActiveSheet.Paste
        
    Next
    
    End Sub

 

[igold]

I see now that you have another worksheet in there, you may have to change

For i = 2 To wsct

to

 For i = 2 To wsct-2

Sorry about that.