VBA to change reference cells of a name range with different cells

IronM

New Member
Joined
Jan 20, 2022
Messages
15
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi all,
I am trying to find a solution to a code (attached to a button) that will change the referenced cells of a Named Range to different cells.
So, I have in the sheet System the cells C6:41 (part of a table) named "plan". I would like to press a button and have the name "plan" refer to cells D6:41 in the same sheet.

Can I get some help pls? :)

Thank you in advance
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi & welcome to MrExcel.
How about
VBA Code:
Sub IronM()
   ThisWorkbook.Names("Plan").RefersTo = Range("Plan").Offset(, 1)
End Sub
 
Upvote 0
Thank you, for the code and the welcoming.

It works as intended but only partially suits my objective. Now, every time I run it, it will go one row further and I need it to revert back to the initial reference. (so just between column C and D - like a toggle).

On a bigger picture: I have another code that runs depending on column C (planned dates). Now, I want to run the same code but for column D, when I press a button/toggle/switch...to run the code on the forecast dates. And so I can change my chart depending on which set of data I need.
 
Upvote 0
In that case
VBA Code:
Sub IronM()
   If Range("Plan").Column = 3 Then
      ThisWorkbook.Names("Plan").RefersTo = Range("Plan").Offset(, 1)
   Else
      ThisWorkbook.Names("Plan").RefersTo = Range("Plan").Offset(, -1)
   End If
End Sub
 
Upvote 0
Solution
Great. This pretty much solves it, with the mention (for those who will search in the future :) ) that "3" is the number of the column where the initial namerange is (A=1, B=2...).

One more thing and we can close this: is there a way to add this code to a 2way button so when I click once it will show Forecast and click second will say Planned and cycle between these 2? Doesn't necessary needs to be a visual change of the button but at least for the text to appear so I know on which column the name range moved to.
 
Upvote 0
As that is a different question, it needs a new thread. Thanks
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top