Adding counter to new worksheet
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. |
Adding counter to new worksheet
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. |
All times are GMT +1. The time now is 07:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com