Copy data from one sheet to another workbook – VBA Excel – debugvba

copy data from one workbook vba excel

Objective

To copy the data from one worksheet of a workbook and paste it into another workbook.

Approach

Here we have first opened the source workbook which contains the data and then we have opened the target workbook where we want to paste the data.After that we have simply copied the data from sheet1 of source workbook and pasted that in sheet1 of another workbook.

Code

Sub CopySheetData()

Dim SourceWorkBook As String
Dim SourceWorkbookName As String

Dim TargetWorkBook As String
Dim TargetWorkBookName As String

'Open source workbook , where data exists
SourceWorkBook = Application.GetOpenFilename(Title:="Open source file", _
FileFilter:="Excel Files (*.xlsx*),")

If Len(SourceWorkBook) = 0 Then
    MsgBox "No file is selected"
    Exit Sub
Else
    Workbooks.Open Filename:=SourceWorkBook
    SourceWorkbookName = ActiveWorkbook.Name
 
 End If
 
'Open target workbook , where data will be pasted
TargetWorkBook = Application.GetOpenFilename(Title:="Open target file", _
FileFilter:="Excel Files *.xlsx* (*.xlsx*),")

If Len(TargetWorkBook) = 0 Then
    MsgBox "No file is selected"
    Exit Sub
Else
    Workbooks.Open Filename:=TargetWorkBook
    TargetWorkBookName = ActiveWorkbook.Name
End If
 
 
'Copy the data from source workbook
Windows(SourceWorkbookName).Activate
Sheets("sheet1").Range("A1:B10").Copy

'Copy the data to target workbook
Windows(TargetWorkBookName).Activate
Sheets("sheet1").Range("A1").Select
ActiveSheet.Paste

'Save source and target workbook
Workbooks(SourceWorkbookName).Save
Workbooks(TargetWorkBookName).Save

'Close source and target workbook
Workbooks(SourceWorkbookName).Close
Workbooks(TargetWorkBookName).Close

End Sub

 

Reference

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

Post you may like

How to count or find occurrence of a character in a string in VBA Excel