How can I combine Multiple Sheets in One Mastersheet while maintaining the Header Data

[Sjoerd33]

Hi I am new on the forum and I hope someone can help me out.
I am just starting with a more extensive use of Excel especially in the field of Macro´s.
I have a Worksheet which contains multiple data sheets. these sheets will be individually updated by the responsable person.
Currently I now manually update the Mastersheet by copying and pasting. I would hope it is possible to have this done with a Makro to update.
I have started with recording a Makro but this is not giving the required result. Also when I try with formulas it is "cleaning" the data of the header which actually is static data.
I have attached the file where I currently am with the Makro, far from perfect....

I hope you can show me a better way.

Thank you

[AB33]

I have attached the code to the button you have.

[Sjoerd33]

Hi,

just been looking at it and it works great, exactly what I needed. Thank you!

[alansidman]

Here is some code for copying and pasting. I did not do the sort as I felt you had that under control and could add it yourself.

Sub Sjoerd()
    Dim w1 As Worksheet
    Dim w2 As Worksheet
    Dim w3 As Worksheet
    Dim w4 As Worksheet
    Set w1 = Sheets("Resumen")
    Set w2 = Sheets("C001")
    Set w3 = Sheets("C002")
    Set w4 = Sheets("C003")
    Dim lr1 As Long
    lr1 = w1.Range("A" & Rows.Count).End(xlUp).Row
    Dim lr2 As Long
    lr2 = w2.Range("A" & Rows.Count).End(xlUp).Row
    Dim lr3 As Long
    lr3 = w3.Range("A" & Rows.Count).End(xlUp).Row
    Dim lr4 As Long
    lr4 = w4.Range("A" & Rows.Count).End(xlUp).Row
    
    Application.ScreenUpdating = False
    w1.Range("A6:Q" & lr1).ClearContents
    lr1 = w1.Range("A" & Rows.Count).End(xlUp).Row
    w2.Range("A2:Q" & lr2).Copy w1.Range("A" & lr1 + 1)
    lr1 = w1.Range("A" & Rows.Count).End(xlUp).Row
    w3.Range("A2:Q" & lr2).Copy w1.Range("A" & lr1 + 1)
    lr1 = w1.Range("A" & Rows.Count).End(xlUp).Row
    w4.Range("A2:Q" & lr2).Copy w1.Range("A" & lr1 + 1)
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.