VBA to Transfer data from one workbook to another

[snowcrash]

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.

 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

 

[Joe4]

As long as you have your complete file paths and names in C3 and C5, this work:

 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

    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:

    Dim wbk As Workbook, wbk1 As Workbook, wbk2 As Workbook

 

[snowcrash]

Joe4'
Thank you so much, so simple and works perfectly!

 

[Joe4]

You are welcome.
Glad I was able to help!