asked on

Automate Excel 2010 to remove tabs, rename tabs & merge

Hello Experts ... I have been tasked with running 3 Excel 2010 reports on 3 different servers. The layout of all the reports are the same including the file name & the names of 15 tabs on each report. Basically I manually remove the tabs I don't need for each report (same tabs every time), add the location prefix to each tab, then move all the tabs from each report into one excel sheet. I do have the ability to name the report file. I'm doing this manually now and it's just a pain. I would like to have some type of automated script (whichever way works) to:

1. Remove tabs by name but not the same on each report. (Ex. File1 - remove tabs A,B,C,D,E; File2 - remove tabs F,G,H, File3 - remove I,J,K)

2. Add same prefix to each tab based on location of server. (Ex. Tab1 = LA-Tab1, Tab2 - LA-Tab2; Tab1 = NY-Tab1, Tab2 = NY-Tab2; Tab1 = FL-Tab1, Tab2 = FL-Tab2, etc)

3. Merge all 3 reports with prefix named tabs into one Excel 2010 spreadsheet.

I hope this is not too confusing. Let me know if you have any questions. Any help would be greatly appreciated as this would save me the task of doing this 3 times a week.

Thanks Experts.

answer_dude

Here's a workbook that will help you control this... warning: this will actually remove the worksheets from your various workbooks -- so be sure to back them up if you want to have your originals preserved.

Also... the file handling for your resulting merged workbook could be better... but I was pressed for time.

Fill out the cells in yellow. Each of your three workbooks can be in different directories, with different names and you can define the prefix that you want to use for each. You also (at the top) can set where you want your resulting merged workbook to be placed/named.

You can list as many "sheets" in each workbook that you want to remove... even if it goes out of the yellow area... however, be sure there are no blank lines separating the tabs to remove:

E.g.,

A
B
C
D

Not

A
B

C

D

mergeBooks-Q27031990.xls

keonh

ASKER

Thanks answer_dude. I have attempted to test it. I added the xlsm extension to each of the workbook name fields. I get an error when clicking on the merge button. (See attached) I did notice the file you sent is xls & not xlsm, could that be the issue?

Will it work on Excel 2010?

Thanks again for your help.

ASKER CERTIFIED SOLUTION

answer_dude

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

keonh

ASKER

Answer_Dude, two words .... YOU ROCK!!!! Thanks. This saved me a lot of time.

keonh

ASKER

Answer_Dude, can you modify this to from 3 sheets to 4. Thanks.

I looked at the code, but not sure if I'm correct or not.

I think the x variable would need to be changed to 1 to 4 in every instance.

answer_dude

I think, technically, you're supposed to post this as another question. But... here you go:

You have to add a new named range (which I did in column E) called Inventory4 and then increase the looping to 4.

This could be enhanced to be more dynamic by cycling through the data in row 4 until it doesn't find another "Workbook" heading... you'd probably get rid of the dynamic named ranges I created and do it differently.

MergeBooks-Q27031990-2.xlsm

keonh

ASKER

Thanks answer_dude ... I will create a new question next time.

Automate Excel 2010 to remove tabs, rename tabs &amp; merge

Automate Excel 2010 to remove tabs, rename tabs & merge