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.
Any help you can provide would be greatly appreciated.
Thanks in advance!
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.
VBA Code:
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!