Help, 52 Worksheets

[Susan]

I created a spreadsheet and made 51 more copies of it. The first work sheet
has Report # 1, now i want to change the report number by 1 on each
spreadsheet, Report 2, Report 3, etc.

Is there a simple formula I can use so i dont have to change all 52
spreadsheets individually?

 

[Gord Dibben]

You have a cell on each sheet that you want Report 1, 2, 3 etc. incremented
across sheets?

Sub Number_Increment()
Dim myword As String
Dim iCtr As Long
myword = "Report "
For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("A1")
.Value = myword & iCtr
End With
Next iCtr
End Sub

You want the sheet names to be Report 1, 2, 3 etc?

Sub RenameTabs()
For I = 2 To Sheets.Count
Sheets(I).Name = "Report " & I
Next
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord

 

[Susan]

Thank you that worked perfectly!!!

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord

 

[Gord Dibben]

You're welcome.

Just curious................what did you want done?

Value in cell or sheet name?


Gord