98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft VBA Training and help » vba courses london - Change A Tab Name on Multiple Workbooks in Different Locations
vba courses london - Change A Tab Name on Multiple Workbooks in Different Locations
Resolved · Low Priority · Version Standard
Thu 19 Apr 2007, 09:46Delegate Simon said...
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.
Thu 19 Apr 2007, 10:01Trainer Carlos said...
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
Thu 19 Apr 2007, 10:53Delegate Simon said...
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.
Thu 19 Apr 2007, 14:35
Edited on Thu 19 Apr 2007, 14:36Trainer Carlos said...
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
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
VBA tip:Empty The Clipboard with CutCopyModeAfter each Copy/Paste operation in VBA, you should use the following line of code to empty the clipboard. This ensures that the computer memory doesn't overload: |
