Merge Multiple Worksheets to One

[Dnass]

Hello,

Very new to VBA so any help is much appreciated!

I have a workbook with over 25 worksheets, of these worksheets I would like to merge 16 of them to one "Master" worksheet.

These 15 worksheets are all in the same format with Row 1 being headings.
Each worksheet is created from a dynamic list of data.

I have found the code below which I have tried using however the problem with this is that it merges ALL worksheets I only want to merge the worksheets that I specify.

Dim J As Integer
 On Error Resume Next
 Sheets(1).Select
 Worksheets.Add
 Sheets(1).Name = "Combined"
 Sheets(2).Activate
 Range("A1").EntireRow.Select
 Selection.Copy Destination:=Sheets(1).Range("A1")
 For J = 2 To Sheets.Count
 Sheets(J).Activate
 Range("A1").Select
 Selection.CurrentRegion.Select
 Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
 Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
 Next
 End Sub

Thank you

[Bishonen]

If there's no logic in the naming pattern ("Data1", "Data2" etc), which would allow to be used in a looping function, you could declare an array with the worksheet names and then go just through each of those.

Dim vNames As Variant

vNames = Array("Sheet1", "Sheet2", "Sheet3")

For i = 0 To UBound(vNames)
    Sheets(vNames(i)).Activate
Next i

In your case that would be:

Dim J As Integer
Dim vNames as Variant

vNames = Array("Sheet1", "Sheet2", "Sheet3")
 On Error Resume Next
 Sheets(1).Select
 Worksheets.Add
 Sheets(1).Name = "Combined"
 Sheets(2).Activate
 Range("A1").EntireRow.Select
 Selection.Copy Destination:=Sheets(1).Range("A1")
 For J = 0 To uBound(vNames)
 Sheets(vNames(J)).Activate
 Range("A1").Select
 Selection.CurrentRegion.Select
 Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
 Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
 Next J
 End Sub

[Dnass]

Great thank you so much this worked perfectly!