NAME SHEETS sequentially

[Guest]

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]

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

 

[Guest]

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

 

[Guest]

Thanks again!

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

 

[Gord Dibben]

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

Thanks again!

Gord Dibben MS Excel MVP