• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 790
  • Last Modified:

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.

0
keonh
Asked:
keonh
  • 4
  • 3
1 Solution
 
answer_dudeCommented:
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
0
 
keonhAuthor Commented:
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? merge error
Thanks again for your help.
0
 
answer_dudeCommented:
Ah... sorry you did say clearly 2010.  Try this one.  It's built using an older version but I've reconfigured to use .xlsm format.
MergeBooks-Q27031990.xlsm
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
keonhAuthor Commented:
Answer_Dude, two words .... YOU ROCK!!!! Thanks. This saved me a lot of time.  
0
 
keonhAuthor Commented:
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.  
0
 
answer_dudeCommented:
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
0
 
keonhAuthor Commented:
Thanks answer_dude ... I will create a new question next time.  
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now