I have 100 sheets(Invoices).
I want to number these Invoices.
I want to name the first sheet #1000 and the rest to be renamed sequentially
after that.
How can I do this with as few keystrokes as possible?

Also, how can I then put that Invoice number in a cell on each sheet?

Enter 1000 in A1 of first sheet.

Right-click and drag down to A100. Release button and "Fill Series".

Run this macro.

Sub NameWS()
'name sheets with list in A1:A100 on first sheet
On Error Resume Next
For i = 1 To 100
Sheets(i).Name = Sheets(1).Cells(i, 1).Value
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 4 Oct 2006 18:38:01 -0700, STEVE
wrote:

I have 100 sheets(Invoices).
I want to number these Invoices.
I want to name the first sheet #1000 and the rest to be renamed sequentially
after that.
How can I do this with as few keystrokes as possible?

Also, how can I then put that Invoice number in a cell on each sheet?

Gord
This guy owes you a CASE of the cold carbonated beverage of your choice.
Lou

"Gord Dibben" wrote:

Enter 1000 in A1 of first sheet.

Right-click and drag down to A100. Release button and "Fill Series".

Run this macro.

Sub NameWS()
'name sheets with list in A1:A100 on first sheet
On Error Resume Next
For i = 1 To 100
Sheets(i).Name = Sheets(1).Cells(i, 1).Value
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 4 Oct 2006 18:38:01 -0700, STEVE
wrote:

I have 100 sheets(Invoices).
I want to number these Invoices.
I want to name the first sheet #1000 and the rest to be renamed sequentially
after that.
How can I do this with as few keystrokes as possible?

Also, how can I then put that Invoice number in a cell on each sheet?

Thanks again!

"Gord Dibben" wrote:

Enter 1000 in A1 of first sheet.

Right-click and drag down to A100. Release button and "Fill Series".

Run this macro.

Sub NameWS()
'name sheets with list in A1:A100 on first sheet
On Error Resume Next
For i = 1 To 100
Sheets(i).Name = Sheets(1).Cells(i, 1).Value
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 4 Oct 2006 18:38:01 -0700, STEVE
wrote:

I have 100 sheets(Invoices).
I want to number these Invoices.
I want to name the first sheet #1000 and the rest to be renamed sequentially
after that.
How can I do this with as few keystrokes as possible?

Also, how can I then put that Invoice number in a cell on each sheet?

Steve

Thanks for the feedback.

I notice that I did not give a response to your question about getting the
sheetname into a cell on each sheet.

One quick way would be to rihgt-click on a sheet tab and "select all sheets".

In any cell enter this formula

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

NOTE: the file must have been saved at least once in order to get the value.

If you want a macro this may get you what you want...............

Sub Date_Increment()
Dim myNum As Long
Dim iCtr As Long
myNum = 1000
For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("A1")
.Value = myNum - 1 + iCtr
End With
Next iCtr
End Sub


Gord

On Thu, 5 Oct 2006 04:08:01 -0700, STEVE
wrote:

Thanks again!

"Gord Dibben" wrote:

Enter 1000 in A1 of first sheet.

Right-click and drag down to A100. Release button and "Fill Series".

Run this macro.

Sub NameWS()
'name sheets with list in A1:A100 on first sheet
On Error Resume Next
For i = 1 To 100
Sheets(i).Name = Sheets(1).Cells(i, 1).Value
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 4 Oct 2006 18:38:01 -0700, STEVE
wrote:

I have 100 sheets(Invoices).
I want to number these Invoices.
I want to name the first sheet #1000 and the rest to be renamed sequentially
after that.
How can I do this with as few keystrokes as possible?

Also, how can I then put that Invoice number in a cell on each sheet?



Gord Dibben MS Excel MVP