Reset Named range. (1 Viewer)

kevlray

Registered User.
Local time
Today, 06:03
Joined
Apr 5, 2010
Messages
1,046
I have an existing named range called TotalActions. But I want to use VBA code to change that range. But the code I have basically does nothing (i.e., the value for the named range TotalActions does not get modified). Everything I have found so far indicates that my code is correct.

With ActiveWorkbook.Names("TotalActions")
.RefersTo = "=AV1!$G5:$G8"
End With

Any suggestions will be appreciated. :banghead:
 

kevlray

Registered User.
Local time
Today, 06:03
Joined
Apr 5, 2010
Messages
1,046
Found out some further information. It appears my code sorta works. I tried a different range and it did change the range, but it is offsetting the rows by four rows. i.e., I set the range to A1:A5 and it shows the range as A5:A9. I have no idea why.
 

kevlray

Registered User.
Local time
Today, 06:03
Joined
Apr 5, 2010
Messages
1,046
Not sure, but it appears it was doing an offset from a active cell. When I set the active cell to A1, it appears to be working correctly now.
 

Trevor G

Registered User.
Local time
Today, 13:03
Joined
Oct 1, 2009
Messages
2,341
You can look to delete the named range before you recreate it, incase it is causing conflict. I would suggest record a macro to find the named range and delete it then create the new named range accordingly and you have the code.
 

Users who are viewing this thread

Top Bottom