Copy the data from one excel file or workbook to another excel file using VBA Excel

Copy data from one excel file to another one using vba

Objective

To copy the data from one excel file to another excel file using VBA Excel.

Approach

Below code opens the source and target workbook, then copies the data from source to target file. Saves the target file with changes, then closes source and target file.

Code

Sub CopyData()
Dim MySourceFile As Variant
Dim MyTargetFile As Variant
'Select and Open a Source file
MySourceFile = Application.GetOpenFilename("Excel Files (*.xlsx), *xlsx")
Workbooks.Open Filename:=MySourceFile
'Provided a temporary name to target file for handling it.
SourceFileTempName = ActiveWorkbook.Name
'Select and Open a Target file
MyTargetFile = Application.GetOpenFilename("Excel Files (*.xlsx), *xlsx")
Workbooks.Open Filename:=MyTargetFile
'Provided a temporary name to target file for handling it.
TargetFileTempName = ActiveWorkbook.Name
'Copy data from source file
Workbooks(SourceFileTempName).Sheets("Sheet1").Range("A1:D4").Copy
'Paste data in target file
Workbooks(TargetFileTempName).Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
'Save the target workbook with the copied data
Workbooks(TargetFileTempName).Save
MsgBox "Your data is copied in target file"
'Close source and target workbook
Workbooks(SourceFileTempName).Close
Workbooks(TargetFileTempName).Close
End Sub


Notes

1.Above code copies range A4:D4 in source file [sheet1] and pastes on cell A1 on target file [sheet1]
2.You can also copy the data if your file is placed in a folder, for that you can define variables for path and apply the same logic.

 

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.application.getopenfilename

Post you may like

Delete all the files from folder and sub folder using VBA Excel

 

 

 

 

 

 

1 comment on “Copy the data from one excel file or workbook to another excel file using VBA Excel

Comments are closed.