If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help, 52 Worksheets
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? |
#2
|
|||
|
|||
Help, 52 Worksheets
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 On Thu, 24 Sep 2009 07:39:02 -0700, Susan wrote: 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? |
#3
|
|||
|
|||
Help, 52 Worksheets
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 InsertModule. 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 ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord On Thu, 24 Sep 2009 08:33:16 -0700, Gord Dibben gorddibbATshawDOTca wrote: 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 On Thu, 24 Sep 2009 07:39:02 -0700, Susan wrote: 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? |
#4
|
|||
|
|||
Help, 52 Worksheets
Thank you that worked perfectly!!!
"Gord Dibben" wrote: 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 InsertModule. 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 ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord On Thu, 24 Sep 2009 08:33:16 -0700, Gord Dibben gorddibbATshawDOTca wrote: 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 On Thu, 24 Sep 2009 07:39:02 -0700, Susan wrote: 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? |
#5
|
|||
|
|||
Help, 52 Worksheets
You're welcome.
Just curious................what did you want done? Value in cell or sheet name? Gord On Thu, 24 Sep 2009 10:28:01 -0700, Susan wrote: Thank you that worked perfectly!!! "Gord Dibben" wrote: 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 InsertModule. 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 ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord On Thu, 24 Sep 2009 08:33:16 -0700, Gord Dibben gorddibbATshawDOTca wrote: 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 On Thu, 24 Sep 2009 07:39:02 -0700, Susan wrote: 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? |
Thread Tools | |
Display Modes | |
|
|