Macro to combine worksheets

S

Scott Bass

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
 
A

AB

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
 
A

AB

This would then also clear the Master up:

Sub CombineWorksheets()

Dim ws_Master As Worksheet

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

ws_Master.Cells.ClearContents

'the rest of all your code.

ExtSub:
On Error GoTo 0
Exit Sub
CreateMasterSheet:
ThisWorkbook.Worksheets.Add.Name = "Master"
Resume


End Sub
 
D

Don Guillett Excel MVP

This would then also clear the Master up:

Sub CombineWorksheets()

    Dim ws_Master As Worksheet

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

    ws_Master.Cells.ClearContents

    'the rest of all your code.

ExtSub:
    On Error GoTo 0
    Exit Sub
CreateMasterSheet:
    ThisWorkbook.Worksheets.Add.Name = "Master"
    Resume

End Sub
Click to expand...

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
 
D

Don Guillett Excel MVP

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
Click to expand...

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
 
A

AB

I'm not sure why but my previous reply posts disapeared...
Nevertheless - this bit of code could get you around the not-duplicate-
master-ws issue + clears its content:

Sub CombineWorksheets()

Dim ws_Master As Worksheet

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

ws_Master.Cells.ClearContents

'the rest of all your code.

ExtSub:
On Error GoTo 0
Exit Sub
CreateMasterSheet:
ThisWorkbook.Worksheets.Add.Name = "Master"
Resume


End Sub
 
S

Scott Bass

"If desired, send your file to dguillett  @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
Click to expand...

Hi Don,

Thanks for the reply. However, didn't I already do all that via my
post???

Re: #3 & #4: How is my pseudo code unclear? Restating, and hopefully
clarifying:

* Go to Sheet 1
* Is its name "Master"
* If not, create a new Sheet 1 named "Master"
* If so, clear its contents
* Loop thru all worksheets except "Master", and optionally skip
specific named worksheets (eg. skip "Foo", "Bar", "Blah", or all
worksheets named "_<whatever>")
* Copy the contents of these worksheets
* If the Copy operation can somehow skip hidden columns, cool. If
not, it's not a requirement, just copy the entire worksheet contents.
* Paste/append these contents into the Master worksheet.

I feel the macro I found via Google is a good starting point, and just
needs to be tweaked slightly. However, I'm not a VBA guru; I was
hoping this would be trivial to someone with VBA skills.

If I need to send this post to your private email, I'm happy to do so
- could you just forward it to yourself instead :-/. That way you'll
know it's not spam ;-)

Thanks,
Scott
 
A

AB

Any idea why my posts are being removed from this discussion? I keep
sending my code samples and they stay on the thread for 10 min or so
and then they disapear...
 
D

Don Guillett Excel MVP

Hi Don,

Thanks for the reply.  However, didn't I already do all that via my
post???

Re: #3 & #4:  How is my pseudo code unclear?  Restating, and hopefully
clarifying:

* Go to Sheet 1
* Is its name "Master"
* If not, create a new Sheet 1 named "Master"
* If so, clear its contents
* Loop thru all worksheets except "Master", and optionally skip
specific named worksheets (eg. skip "Foo", "Bar", "Blah", or all
worksheets named "_<whatever>")
* Copy the contents of these worksheets
* If the Copy operation can somehow skip hidden columns, cool.  If
not, it's not a requirement, just copy the entire worksheet contents.
* Paste/append these contents into the Master worksheet.

I feel the macro I found via Google is a good starting point, and just
needs to be tweaked slightly.  However, I'm not a VBA guru; I was
hoping this would be trivial to someone with VBA skills.

If I need to send this post to your private email, I'm happy to do so
- could you just forward it to yourself instead :-/.  That way you'll
know it's not spam ;-)

Thanks,
Scott- Hide quoted text -

- Show quoted text -
Click to expand...

Just follow my instructions that I gave to send to me.
 
S

Scott Bass

Just follow my instructions that I gave to send to me.
Click to expand...

No thanks, I'll buy an O'Reilly book first. Your instructions are
inane, regardless of whether you're an MVP.

Anyone else?
 
D

Don Guillett Excel MVP

No thanks, I'll buy an O'Reilly book first.  Your instructions are
inane, regardless of whether you're an MVP.

Anyone else?- Hide quoted text -

- Show quoted text -
Click to expand...

Sorry that I just don't have time to RE-create yoiur project just to
test... I get many requests so I have to try to get as much info as
possible to assist. Sure as hell, I do what I think? you need only to
have you say, that's not it... Good luck. I don't know o'reilly....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copying Filtered Data from multiple worksheets 3
Macro to Combine Data from Multiple Sheets with Cell References 4
Error Message - Macro to Unlink MS Queries from Workbook 0
Combining Multilple Worksheets Into One 6
Combine three InputBoxes into one? 7
Combine columns from seperate sheets into new sheet- macro improve 4
conso macro 0
Macro will copy formula instead of value 6

Top