Simple Question I hope?

[MichaelRSnow]

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

 

[Trevor G]

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]

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]

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]

Perhaps:-

ActiveWorkbook.names("Check1").RefersToRange.Copy Workbooks("WorkbookB.xls").names("Check1").RefersToRange

 

[MichaelRSnow]

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]

Perhaps:-

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]

Try:-

 ActiveWorkbook.names(Range("l3").Value).RefersToRange.Copy Workbooks("WorkbookB.xls").names("Check1").RefersToRange

 

[MichaelRSnow]

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]

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)

ActiveWorkbook.names(Range("B1").Value).RefersToRange.Copy _
Workbooks("BookB.xls").names(Range("B1").Value).RefersToRange