Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine worksheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine worksheets
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine worksheets
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine worksheets
On Jul 1, 6:52*am, AB wrote:
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 "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." |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine worksheets
On Jul 1, 12: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 "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." |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine worksheets
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 On Jul 1, 1:19*pm, Don Guillett Excel MVP wrote: On Jul 1, 12: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 "If desired, send your file to dguillett 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."- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine worksheets
On Jul 1, 10:19*pm, Don Guillett Excel MVP
wrote: On Jul 1, 12: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 "If desired, send your file to dguillett 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." Hi Don, Thanks for the reply. However, didn't I already do all that via my post??? #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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine worksheets
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... On Jul 1, 1:47*pm, Scott Bass wrote: On Jul 1, 10:19*pm, Don Guillett Excel MVP wrote: On Jul 1, 12: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 "If desired, send your file to dguillett 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." Hi Don, Thanks for the reply. *However, didn't I already do all that via my post??? #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 - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine worksheets
On Jul 1, 7:47*am, Scott Bass wrote:
On Jul 1, 10:19*pm, Don Guillett Excel MVP wrote: On Jul 1, 12: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 "If desired, send your file to dguillett 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." Hi Don, Thanks for the reply. *However, didn't I already do all that via my post??? #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 - Just follow my instructions that I gave to send to me. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine worksheets
On Jul 2, 12:53*am, Don Guillett Excel MVP
wrote: On Jul 1, 7:47*am, Scott Bass wrote: On Jul 1, 10:19*pm, Don Guillett Excel MVP wrote: On Jul 1, 12: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 "If desired, send your file to dguillett 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." Hi Don, Thanks for the reply. *However, didn't I already do all that via my post??? #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 - Just follow my instructions that I gave to send to me. No thanks, I'll buy an O'Reilly book first. Your instructions are inane, regardless of whether you're an MVP. Anyone else? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine worksheets
On Jul 1, 10:11*am, Scott Bass wrote:
On Jul 2, 12:53*am, Don Guillett Excel MVP wrote: On Jul 1, 7:47*am, Scott Bass wrote: On Jul 1, 10:19*pm, Don Guillett Excel MVP wrote: On Jul 1, 12: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 "If desired, send your file to dguillett 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." Hi Don, Thanks for the reply. *However, didn't I already do all that via my post??? #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 - Just follow my instructions that I gave to send to me. 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 - 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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I combine worksheets w/o enough rows to combine? | Excel Worksheet Functions | |||
Combine worksheets in multiple workbook in one workbook with a macro | Excel Discussion (Misc queries) | |||
Macro to combine worksheets data - overwrites existing | Excel Discussion (Misc queries) | |||
How to merge / combine several worksheets into one new worksheet without VBA / Macro? FOR EXPERTS | Excel Worksheet Functions | |||
How to combine several worksheets | Setting up and Configuration of Excel |