VBA combining some (not all) sheets within a workbook

[amunrohanover]

Hi, I need to combine contents to 7 of the 10 worksheets in a workbook that all have the same column headings. I would like it to combine on a master sheet that i can go in and refresh once a month. Please Help!!

 

[mumps]

What are the names of the 7 sheets you want to combine?

 

[amunrohanover]

What are the names of the 7 sheets you want to combine?

Bedard
Luna
Corbett
Harper
McGorman
McIsaac
Pace

 

[mumps]

Make sure you have a sheet named "Master" and then try this macro:

Sub CombineSheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, i As Long, desWS As Worksheet
    Set desWS = Sheets("Master")
    Sheets("Bedard").Rows(1).Copy desWS.Cells(1, 1)
    For Each ws In Sheets(Array("Bedard", "Luna", "Corbett", "Harper", "McGorman", "McIsaac", "Pace"))
        ws.UsedRange.Offset(1, 0).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[amunrohanover]

Works!!! Thanks!!!

 

[mumps]

You are very welcome.