Simon has attended:
Excel VBA Intro Intermediate course
Change A Tab Name on Multiple Workbooks in Different Locations
Dear Sir / Madam,
I have 32 Spreadsheets. They are all named differently but all end in the word "Commentary". Each Speadsheet has 2 tabs only. One of these tabs on each spreadsheet is labelled "Period 1".
Each Month I have to go through each Spreadsheet and change the "Period 1" to "Period 2" and so forth...
I am trying to write a procedure which assigned to a Command Button, will automatically change the above on all 32 Spreadsheets.
I managed to find some code on the net which searches my folder and finds all Spreadsheets which contains the word "Commentary", but then I can't manage to write a procedure after this for it to change the relevant tab.
Am I heading in the right direction? Any help or advice on this would be greatfully received and very much appreciated.
Kind Regards,
Simon.
RE: Change A Tab Name on Multiple Workbooks in Different Locatio
Simon
To channge the Tab name on a sheet use the following code:
Open the relevant Workbook then
Sheets("Period 1").Select
ActiveSheet.Name = "Period 2"
This changes the name of the sheet to Period 2
Hope this helps
Regards
Carlos
RE: Change A Tab Name on Multiple Workbooks in Different Locatio
Hello Carlos,
Thank you very much for the swift reply in response to my question.
This works perfectly.
However could I possibly ask how I can combine this statement to change all of the tabs in my spreadsheets with one click. What I would like is a command button on a new spreadsheet which I click once and then this opens all the spreadsheets say one at a time and changes the tab then saves and closes it.
Apologies for requesting more information. Hope you can help?
Kind Regards,
Simon.
RE: Change A Tab Name on Multiple Workbooks in Different Locatio
Simon
You need to create a Sub routine that contains a loop that opens all the Workbooks one at a time and changes the tab name eg
Create a variable eg Dim MyWorkbook as String
And load into it, the Workbook
