I am adding a record number into column A of a data matrix, and the
number of rows in the matrix varies. I have the following code to do
this:

With Worksheets("WorksheetName")
For i = 2 To .Range("B65536").End(xlUp).Row
Cells(i, 1).Value = i - 1
Next i
End With

My problem is the the macro just added the worksheet where the data
exists, so I don't know it's name. Since this macro is iterative and
creates several such sheets the worksheet name keeps changing. How
do I make this macro work when the worksheet name is different every
time?

Thanks for the help.

Somewhere in the code if you are adding sheets you will have something like

Worksheets.Add

All you need to do is declare a worksheet object at the start of the routine

Dim theWorksheetINeedToKnowTheNameOf as Worksheet
'I'm a little over the top with the variable name but...

the when you add a sheet use

Set theWorksheetINeedToKnowTheNameOf =Worksheets.Add

Now you don't need to now the name you can just use the variable name

theWorksheetINeedToKnowTheNameOf .Range("B65536).End.......etc

You could of course gat the name using

shtName=theWorksheetINeedToKnowTheNameOf .Name

Worksheets(shtName).Range......etc

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk

wrote in message
oups.com...
I am adding a record number into column A of a data matrix, and the
number of rows in the matrix varies. I have the following code to do
this:

With Worksheets("WorksheetName")
For i = 2 To .Range("B65536").End(xlUp).Row
Cells(i, 1).Value = i - 1
Next i
End With

My problem is the the macro just added the worksheet where the data
exists, so I don't know it's name. Since this macro is iterative and
creates several such sheets the worksheet name keeps changing. How
do I make this macro work when the worksheet name is different every
time?

Thanks for the help.