A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help, 52 Worksheets



 
 
Thread Tools Display Modes
  #1  
Old September 24th, 2009, 03:39 PM posted to microsoft.public.excel.newusers
Susan
external usenet poster
 
Posts: 651
Default 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  
Old September 24th, 2009, 04:33 PM posted to microsoft.public.excel.newusers
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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  
Old September 24th, 2009, 04:40 PM posted to microsoft.public.excel.newusers
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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  
Old September 24th, 2009, 06:28 PM posted to microsoft.public.excel.newusers
Susan
external usenet poster
 
Posts: 651
Default 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  
Old September 24th, 2009, 06:33 PM posted to microsoft.public.excel.newusers
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:18 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.