Using VBA to use the value of a Named Range in one Workbook to define the Workbook ("Name") for another Workbook

[Lexcon07]

I'm trying to use VBA to copy and paste a range of cell from one workbook to another while being able to easily change the name of the destination file.

What I am trying to do is use a Named Range/Cell (i.e. "DestinationFileName") in the original Excel file (i.e. "Source File.xlsm") to define the name of the file I want to paste the information to.

For example, in the script below, I am copy data from the "Source File.xlsm" workbook and pasting it to the "Destination File.xlsm" workbook. I would like to use the Named Range/Cell (i.e. "DestinationFileName") in the "Source File.xlsm" workbook to define the name of the destination file.

Sub ExportRange1()
    Application.ScreenUpdating = False
        Workbooks("Source File.xlsm").Worksheets("ExportSheet").Range("ExportRange1").Copy
        Workbooks("Destination File.xlsm").Worksheets("ImportSheet").Range("A2").PasteSpecial Paste:=xlPasteValues
    Application.ScreenUpdating = True
End Sub

Any help you can provide would be greatly appreciated.

Thanks in advance!

 

[Yongle]

Can use shorter notation with named ranges

DestinationFile is a named range pointing at ONE cell whose value in my test was "File1.xlsx" (ie the file name INCLUDING the string)
- if the extenstion is not in the cell then amend dPath string like this

dPath = "C:\folder\subfolder" &  "\" & [DestinationFile] & ".xlsx"

ExportRange is your existing named range

Set Source BEFORE opening destination file

Assigning values is faster than copy\paste if formatting etc is not required
(if otherwise, use your original notation)

Source.Copy : dWb.Sheets("ImportSheet").Range("A2")

    Dim dWb As Workbook, dPath, Source As Range
    dPath = "C:\folder\subfolder" &  "\" & [DestinationFile]
    Set Source = [ExportRange]
    Set dWb = Workbooks.Open(dPath)
    dWb.Sheets("ImportSheet").Range("A2").Resize(Source.Rows.Count, Source.Columns.Count).Value = Source.Value
    dWb.Save
    dWb.Close False

 

[Lexcon07]

Hi Tongle,

Thank you for your help!

I had to make a couple modifications, but it's working now!

• The DestinationFileName cell contains the complete file location, just in case the location of the files ever changes;
• I was getting a error relating the line of code Set Source = [ExportRange], so I changed it to Set Source = Range("ExportRange") and it works now

Sub ExportRange()
    Dim dWb As Workbook, dPath, Source As Range
    dPath = [DestinationFileName]
    Set Source = Range("ExportRange")
    Set dWb = Workbooks.Open(dPath)
    Set Destination = Workbooks.Open(dPath)
        ThisWorkbook.Worksheets("SourceData").Range("ExportRange").Copy
        Destination.Sheets("ImportSheet").Range("A2").PasteSpecial Paste:=xlPasteValues
    dWb.Save
End Sub

Thanks again!

 

[Lexcon07]

Sorry "Yongle", typo...

 

[Yongle]

thanks for your feedback