It's a handful but you've phrased it quite well.
Unfortunatelly i don't have enough time to deal with all your requests
but this one would deal with the Master-worksheet issue:

Sub CombineWorksheets()

Dim ws_Master As Worksheet

On Error GoTo CreateMasterSheet:
Set ws_Master = ThisWorkbook.Worksheets("Master")
On Error GoTo 0

'the rest of all your code.

ExtSub:
On Error GoTo 0
Exit Sub

CreateMasterSheet:
ThisWorkbook.Worksheets.Add.Name = "Master"
Resume

End Sub



On Jul 1, 6:18*am, Scott Bass wrote:
Hi,

I need to combine/append the contents of multiple worksheets into a
single worksheet.

I Googled for “excel macro to combine worksheets”, and found the
following macro:

Sub CombineWorksheets()
* * Dim J As Integer

* * On Error Resume Next
* * Sheets(1).Select
* * Worksheets.Add ' add a sheet in first place
* * Sheets(1).Name = "Master”

* * ' copy headings
* * Sheets(2).Activate
* * Range("A1").EntireRow.Select
* * Selection.Copy Destination:=Sheets(1).Range("A1")

* * ' work through sheets
* * For J = 2 To Sheets.Count ' from sheet 2 to last sheet
* * * * Sheets(J).Activate ' make the sheet active
* * * * Range("A1").Select
* * * * Selection.CurrentRegion.Select ' select all cells in this
sheets

* * * * ' select all lines except title
* * * * Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

* * * * ' copy cells selected in the new sheet on last line
* * * * Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)
(2)
* * Next
End Sub

HOWEVER, I would like to enhance it in accordance with the following
pseudocode:

• * * Go to Worksheet 1
• * * Is its name “Master”?
• * * If not, create it. *If yes, then use it *(don't keep creating
"Master" over and over...)
• * * Clear the contents of “Master”
• * * Loop through all the worksheets, copy their contents (cells with
data only, not blank rows - sometimes Excel gets confused where the
last cell is located), then append those contents to “Master”
• * * For the above, is it possible to copy only unhidden columns? *Hidden
columns would be skipped. *This is a nice to have, not critical.
• * * Write the macro in such a way to exclude certain worksheets. *For
example, a parameter with a list of worksheets, then something like
“If Worksheet.Name In (List of Excluded Worksheets) then skip”. *Or
perhaps skip all worksheets with a particular naming convention (eg.
begins with underscore).
• * * Parameterize whether to keep or skip the first row, and whether to
keep the first row from the first (copied) worksheet.

Thanks for any help or hints you can provide.

Regards,
Scott