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.

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

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.

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’s path name one at a time, from a list. Then create a loop similar to the one below: (I’ve used the DO loop but you can try others)

NB This is a guideline only. You will need to adapt it to your situation

My Workbook = 1st Workbook on list

Do While MyWorkbook <> “”

Workbooks.Open Filename:= MyWorkbook eg. "c:\My Docs\book1.xls"

Sheets("Period 1").Select
ActiveSheet.Name = "Period 2"

Activeworkbook.Save
Activeworkbook.Close


MyWorkbook = The next workbook

Loop

Then create the command button and call the above routine from it.

Regards

Carlos