referencing a changing sheet name

[xtraxmechx]

Hello All,

I have a work book that has multiple types of parts. I then run a macro that pull all the different sheets together in to one sheet tiltled "master price list, mmm-dd-yyyy". Im trying to make sure that everything has copied correctly. My rational is to simply use the count function on all the old sheets VS. the new sheet. I run in to a problem when I try and reference the new sheet as the date is always changing.

Any and all help is appreciated.

j

 

[stnkynts]

Not sure from your post but if the sheet in question is not moving in its place relative to the other sheets before it you can just use the sheet index number instead of the name.

For example (Assuming "Sheet1" is the default label for the first sheet in workbook):

Sheets("Sheet1").Range("A1").Value

Is the same as:

Sheets(1).Range("A1").Value

 

[CWatts]

If you always launch the macro from the merged sheet, you can use something like below to store the sheet name in a macro.

ActiveSheet.Name

Or if you want to launch it from anywhere, but the sheet is always in a certain order in your workbook, you coudl reference it by it's index location.

Worksheets(1).Name

Hope those suggestions help.

 

[xtraxmechx]

Sorry I want clear enough. I'm looking to type this in to the formula bar not in a macro.

I.e. something like this.

=count(worksheet(1)K5:Q13000)

This would then return the number of cells with numbers in the array in the first worksheet.

Right now I have

=count('Master Price List JUL-18-2011'!K5:13000)

While this works now, if I were to run the macro tommorow the price list's date would change thus making a error as this sheet would no longer exist.