To copy the data from one excel file to another excel file using VBA Excel.
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.
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
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.
Post you may like