VBA to Combine Sheets Dynamically Based on Sheet Name

[_Nemo_]

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.

 

[mumps]

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.

 

[brawnystaff]

Probably your best bet is to use the Excel addin "RDBMerge" from Ron de Bruin's website

RDBMerge, Excel Merge Add-in for Excel for Windows