Automate Excel 2010 to remove tabs, rename tabs & merge
Posted on 2011-05-11
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.