Loop each sheet then paste data insert cell down to Master sheet

[SamKhem]

Dear Senior member

I would like request you to guide vba code as example: loop sheet1 to sheet3 then paste data insert cell down to sheet "Master" as information below

Sheet1				Sheet2				Sheet3				Master
No.	Menu 1	Menu 2		No.	Menu 1	Menu 2		No.	Menu 1	Menu 2		No.	Menu 1	Menu 2
1	abc	abc		1	hello	hello		1	Good	Good		1	abc	abc
2	abc	abc		2	hello	hello		2	Good	Good		2	abc	abc
3	abc	abc		3	hello	hello		3	Good	Good		3	abc	abc
4	abc	abc		4	hello	hello		4	Good	Good		4	abc	abc
5	abc	abc		5	hello	hello						5	abc	abc
6	abc	abc		6	hello	hello						6	abc	abc
7	abc	abc										7	abc	abc
8	abc	abc										8	abc	abc
9	abc	abc										9	abc	abc
10	abc	abc										10	abc	abc
11	abc	abc										11	abc	abc
12	abc	abc										12	abc	abc
13	abc	abc										13	abc	abc
												14	hello	hello
												15	hello	hello
												16	hello	hello
												17	hello	hello
												18	hello	hello
												19	hello	hello
												20	Good	Good
												21	Good	Good
												22	Good	Good
												23	Good	Good

 

[Joe4]

What ranges will this data appear in?
Will the lists always be in columns A and B, and start at the very top (row 1)?

 

[Joe4]

If all my assumptions are above are correct, this code should do what you want.

Sub CombineData()

    Dim ws As Worksheet
    Dim lr As Long
    
    Application.ScreenUpdating = False
    
'   Loop through all worksheets
    For Each ws In ActiveWorkbook.Worksheets
'       Check to see if it is not Master sheet
        If ws.Name <> "Master" Then
'           Copy data to bottom of Master sheet
            ws.Range("A1").CurrentRegion.Offset(1, 0).Copy Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next ws
    
'   Find last row in column A with data on Master sheet
    lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
'   Populate counter formula in column A of Master sheet
    Sheets("Master").Range("A2:A" & lr).FormulaR1C1 = "=ROW()-1"
    
    Application.ScreenUpdating = True
    
End Sub

 

[SamKhem]

If all my assumptions are above are correct, this code should do what you want.

Sub CombineData()

    Dim ws As Worksheet
    Dim lr As Long
   
    Application.ScreenUpdating = False
   
'   Loop through all worksheets
    For Each ws In ActiveWorkbook.Worksheets
'       Check to see if it is not Master sheet
        If ws.Name <> "Master" Then
'           Copy data to bottom of Master sheet
            ws.Range("A1").CurrentRegion.Offset(1, 0).Copy Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next ws
   
'   Find last row in column A with data on Master sheet
    lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
'   Populate counter formula in column A of Master sheet
    Sheets("Master").Range("A2:A" & lr).FormulaR1C1 = "=ROW()-1"
   
    Application.ScreenUpdating = True
   
End Sub

Worked perfect. Thank for support.
Have a nice weekend!

 

[Joe4]

You are welcome.
Glad I was able to help!