Change Named Range reference using Codename of sheet

[zsnemeth]

Dear All,

could anyone please help me to change my named range cell reference, by using the codename of the particular sheet?

This is what works fine, but uses the name of the sheet:

ActiveWorkbook.Names("myRng").RefersTo = "='User123'!$B$39:$B$" & LastRow

where the "LastRow" is a variable, what changes.

I would like to create the same, but I need to use the codename of the sheet, which is: UserID

I tried this:
ActiveWorkbook.Names("myRng").RefersTo = "='" & UserID.Name & "'!$B$39:$B$" & LastRow
but it did not work.

Could you please help me?

Thank you in advance!

Happy Easter by the way!

Cheers,
Zs

 

[Norie]

That code works fine for me, what problems are you having?

 

[JLGWhiz]

Dear All,

could anyone please help me to change my named range cell reference, by using the codename of the particular sheet?

This is what works fine, but uses the name of the sheet:

ActiveWorkbook.Names("myRng").RefersTo = "='User123'!$B$39:$B$" & LastRow

where the "LastRow" is a variable, what changes.

I would like to create the same, but I need to use the codename of the sheet, which is: UserID

I tried this:
ActiveWorkbook.Names("myRng").RefersTo = "='" & UserID.Name & "'!$B$39:$B$" & LastRow
but it did not work.

Could you please help me?

Thank you in advance!

Happy Easter by the way!

Cheers,
Zs

Show us how you have UserID declared in the Dim statement and how it is initialized..

 

[zsnemeth]

Aaah, my God!
It was simply a "typo", because here to this post I did not copy my code, just typed it in, and I did not recognise, that in the editor it had a ) at the end, which was too much.
Now the code works fine!

Thank you for the tip, that it did work for you, so I did not search for an other solution, just searched - and found - the difference.

Cheers!

 

[zsnemeth]

Show us how you have UserID declared in the Dim statement and how it is initialized..

I am afraid I do not understand what you would like to see, but the code works now, so the question has actually been answered. It was my fault, a typo... But thank you!

 

[JLGWhiz]

I am afraid I do not understand what you would like to see, but the code works now, so the question has actually been answered. It was my fault, a typo... But thank you!

Since you found your problem, the information is not necessary, but for future referencce, here is what I meant. UserId appears to be an Object variable for a worksheet. So it would need to be declared as

Dim UserID As Worksheet

Then to initialize the variable you would need

Set UserId = Sheets("Data")

as am example. I just wanted to make sure you had all the correct steps in your code and apparently you do.
Regards, JLG