Adding counter to new worksheet

[vrzimmerm]

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.

 

[Nick Hodge]

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
(e-mail address removed)
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