Simple Question I hope?

MichaelRSnow

Active Member
Joined
Aug 3, 2010
Messages
382
How do I go about copying value within a named range to another named range within another workbook via VBA

e.g.

Workbook A, Sheet1, Cell A1 named 'Check1' contains value 01/01/2011
Workbook B, Sheet1, Cell A1 named 'Check1' no value in cell

copy value '01/01/2011' via named ranges from from workbook 1 to workbook 2

Thanks everyone
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,700
Office Version
  1. 2016
Platform
  1. Windows
Record a macro to do this. Sample indicated below (might not be the most elegant but works)

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro1()<br><SPAN style="color:#007F00">'Recorded Macro</SPAN><br>    Application.Goto Reference:="check1"<br>    Selection.Copy<br>    Workbooks.Open Filename:="C:\Quote\names.xls"<br>    Application.Goto Reference:="check1"<br>    ActiveSheet.Paste<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

MichaelRSnow

Active Member
Joined
Aug 3, 2010
Messages
382
Not sure I was 100% clear on this Trevor, missed out a key part

:="check1" can the name of the named range be picked up from another cell?

i.e.


Application.Goto Reference:="check1" pull the named range 'check 1' from the value within cell B1

The named range will be what ever value is in cell B1?

 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,700
Office Version
  1. 2016
Platform
  1. Windows
I don't understand your question. If you have a named range in both books then the goto command is giving you the solution. You have quoted A1 in both books and actually it doesn't matter which cell as it is using the name.

If you want something different then please explain step by step.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841

ADVERTISEMENT

Perhaps:-
Code:
ActiveWorkbook.names("Check1").RefersToRange.Copy Workbooks("WorkbookB.xls").names("Check1").RefersToRange
 

MichaelRSnow

Active Member
Joined
Aug 3, 2010
Messages
382
Its difficult to explain. I have a log book which creates a basic template based on answer in the log book.

My code creates the named ranges i.e. the value in L3 is Date1, so it names the range B4 'Date1' within Workbook1, the value 'Date1' changers everytime a new basic template is created from the log book

'The Code for this change is'
ActiveWorkbook.Names.Add Name:=Range("L3").Value, RefersToR1C1:="=Report!R4C2"

I can't fix the named range with the template VBA as the template updates the log book and there could be many templates created with different named ranges.

Finding this really hard to explain.
 

MichaelRSnow

Active Member
Joined
Aug 3, 2010
Messages
382

ADVERTISEMENT

Perhaps:-
Rich (BB code):
ActiveWorkbook.names("Check1").RefersToRange.Copy Workbooks("WorkbookB.xls").names("Check1").RefersToRange

Thanks Mick

How do I get the 'named range' highlighted in red above to be the value keyed into cell L3 within the ActiveWorkbook, i.e. look up the name of the range?
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try:-
Code:
 ActiveWorkbook.names(Range("l3").Value).RefersToRange.Copy Workbooks("WorkbookB.xls").names("Check1").RefersToRange
 

MichaelRSnow

Active Member
Joined
Aug 3, 2010
Messages
382
Thanks I tried yours and tweaked in slightly but can't get passed a run time error 9, subscript out of range? any advice

ActiveWorkbook.Names(Range("B1").Value).RefersToRange.Copy Workbooks("BOOKB").Worksheets("Sheet1").Names(Range("B1").Value).RefersToRange.Paste.Value

Cheers
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
You need to have both Books open and you must declare the extension to the workbook as shown below. (Whatever it is !!!)
Just copy below and alter the Bookname "BookB.xls" with its extension.(whatever)
Code:
ActiveWorkbook.names(Range("B1").Value).RefersToRange.Copy _
Workbooks("BookB.xls").names(Range("B1").Value).RefersToRange
 

Watch MrExcel Video

Forum statistics

Threads
1,122,217
Messages
5,594,887
Members
413,947
Latest member
gizmolucy

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
Top