Copy Named Range Data of the workbook to Another workbook by VBA

Question

I want to copy a Named Range from one workbook to another using VBA.

There are three files:

1. MACRO_PLACED.XLSM <<<< Macro is here

2. SOURCE_DATA.XLSX <<<< Data is here

3. DESTINATION_DATA.XLSX <<<< Data to be copied here

Initially, only the first file is opened and the other two files are left closed.

The source_data workbook has multiple sheets, and the destination_data workbook has the same structure as source_data. In both of these files there is a Named Range on Sheet1, for example if the data is in "A1:C20" I name that range "RESULT"

How do I copy that particular named range to the destination file using a VBA function in the first file?

Answer 1

If we want to copy a range from one place to another we can use:

r1.Copy r2

Now all we need is a framework to allow this line to be executed:

Sub KopyPaste()
    Dim r1 As Range, r2 As Range, w1 As Workbook, w2 As Workbook

    Set w2 = Workbooks.Open(Filename:="C:\TestFolder\DESTINATION_DATA.xlsx")

    Set w1 = Workbooks.Open(Filename:="C:\TestFolder\SOURCE_DATA.xlsx")
    Set r1 = Range("RESULT")
    sheetname = r1.Parent.Name
    addy = r1.Address

    Set r2 = w2.Sheets(sheetname).Range(addy)

    r1.Copy r2
End Sub

At this point you can elect to save the destination if you want.

Answer 2

You can open both files first and then save and close them after you copied the range. Here is a way you can do that. It should work like this:

Sub Copying()

Workbooks.Open ("INSERT FILE LOCATION HERE/SOURCE_DATA.XLSX")
Workbooks.Open ("INSERT THE OTHER FILE LOCATION HERE/DESTINATION_DATA.XLSX")

// Now Insert the code where you copy things (Don't forget to refer to your 
// the specific workbook

Workbooks("SOURCE_DATA.XLSX").Close SaveChanges:=True
Workbooks("DESTINATION_DATA.XLSX").Close SaveChanges:=True

End Sub