VBA to Combine Sheets Dynamically Based on Sheet Name

_Nemo_

New Member
Joined
Nov 20, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I’ve been searching the forums but may not know the exact way to phrase my question. I’ve got a workbook with multiple tabs (at times up to 100) that each contain one page of a document. Some documents are single paged and others are multiple pages. Each tab’s data is in the exact same format/location. I’m am trying to find a VBA script that will combine the multipage documents based on the tab name.

Requirements:

  • Delete the template tab
  • Combine DocA-1, DocA-2, DocA-3 as DocA and then delete DocA-1, DocA-2, DocA-3
  • Data would need to append in order; the dash is a page number
  • Leave DocB and DocC as is
  • Combine DocD-1 and DocD-2 as DocD and then delete DocD-1 and DocD-2
  • Data would need to append in order; the dash is a page number
  • Print each sheet/tab to PDF with the sheet name becoming the file name
Because Doc names and the number of tabs change frequently vary I’m hoping this can be done dynamically so the script looks at the current sheets to complete the if/then logic. Most of that I’ve found assumes the sheet names will be static.

I’ve found VBA to combine all sheets (Combine Data from Multiple Sheets to A Sheet | Dan Wagner Co) but I need the code to look at the sheet name.

I’ve also found code to complete the PDF printing (Save Excel as PDF with VBA - Excel Off The Grid) but I am not sure how to tie it all together.

Thank you for any help you can provide.
 

Attachments

  • samplefile.PNG
    samplefile.PNG
    2.3 KB · Views: 11

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,307
I can't promise a solution but it would be easier for Forum members to help if you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,904
Messages
5,638,907
Members
417,058
Latest member
BRYCEPIETROWIAK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top