VBA to Transfer data from one workbook to another

snowcrash

New Member
Joined
Mar 1, 2018
Messages
10
Hi all,

Still learning VBA and this may be a really basic question for all, but I am struggling to find the answer while surfing. Would really appreciate some simple explained help to learn.

TASK
I want to transfer data from one workbook to another. using a "transfer tool" which i can email out, and the filename locations of the source and destination files are variables and defined in the "Transfer tool"
In total i will have 3 workbooks
wbk "transfer tool", which is where the code is stored, and the file paths name as variable inputs
wbk1 "source data"
wbk2 "destination for data"

I can get the data to transfer, however this is only when i put the file location in the code.
I want to replace the file location as a variable input for in the wbk "transfer tool" cell "C3" = Source data Filename and cell "C5" = Destination Filename

I currently have the following code, and every time i try to replace the Filenames as input in the wbk "Transfer tool" i get errors.. and the code doesn't work.


Code:
 Sub DataTransfer()


 Dim wbk, wbk1, wbk2 As Workbook


    'Workbook with VBA in it.
    Set wbk = ActiveWorkbook


    'Define souce workbook
    Set wbk1 = Workbooks.Open(Filename:="C:\Users\person\Desktop\OLDFILE.xlsm")
    'Define destination workbook
    Set wbk2 = Workbooks.Open(Filename:="C:\Users\person\Desktop\NEWFILE.xlsm")




    Call wbk1.Worksheets("Sheet1").Range("S18:X22").Copy
    Call wbk2.Worksheets("sheet1").Range("S18:X22").PasteSpecial(xlPasteValues)
    Application.CutCopyMode = False




    End Sub
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,388
Office Version
  1. 365
Platform
  1. Windows
As long as you have your complete file paths and names in C3 and C5, this work:
Code:
 Sub DataTransfer()

    Dim wbk As Workbook, wbk1 As Workbook, wbk2 As Workbook
    Dim file1 As String, file2 As String
    
    'Get file names from sheet
    file1 = Range("C3")
    file2 = Range("C5")

    'Workbook with VBA in it.
    Set wbk = ActiveWorkbook

    'Define souce workbook
    Set wbk1 = Workbooks.Open(Filename:=file1)
    'Define destination workbook
    Set wbk2 = Workbooks.Open(Filename:=file2)

    Call wbk1.Worksheets("Sheet1").Range("S18:X22").Copy
    Call wbk2.Worksheets("sheet1").Range("S18:X22").PasteSpecial(xlPasteValues)
    Application.CutCopyMode = False

End Sub
Also note that this does not do what you think:
Dim wbk As Workbook, wbk1 As Workbook, wbk2 As Workbook
Code:
    Dim wbk, wbk1, wbk2 As Workbook
It will declare wk2 as Workbook, but the other two will be variant. Each variable declaration needs to be explicit, like this:
Code:
    Dim wbk As Workbook, wbk1 As Workbook, wbk2 As Workbook
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,388
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help!
:)
 

Forum statistics

Threads
1,137,154
Messages
5,679,914
Members
419,862
Latest member
Bluewings666

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