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.
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