Macros for copying data from a workbook with multiple sheet to another workbook with multiple sheet

Question

How to VBA code if I want to copy data from one excel workbook which has data in 3 different sheets to be copied to different excel workbook with 3 sheets?

I have 2 excel workbook named as W1 and W2, each excel workbook consists of 3 sheets named as A1, A2, A3 and B1, B2, B3 respectively.

W1 would be an open workbook and W2 would be a closed workbook in a specific path.

I need to copy selected data from

W1 A1 to W2 B1 W1 A2 to W2 B2 W1 A3 to W2 B3

The selected data would be a single cell data (cell H5 and N3) and multiple cell data (cell B12 to Q21)

After data copy and paste, need to save as to create W3 (workbook 3)

score 0 · Accepted Answer · answered Jul 15 '20 at 15:33

It is not clear whether you want to copy the exact same ranges of cells on the three worksheets or not. However, below is some basic VBA that performs this for one worksheet:

Sub sExportSelectedData()
    On Error GoTo E_Handle
    Dim wbSource As Workbook
    Dim wbDest As Workbook
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim strFile As String
    strFile = "C:\test\export.xlsx"
    Set wbSource = ThisWorkbook
    Set wbDest = Workbooks.Open("C:\test\book2.xlsx")
    
    Set wsSource = wbSource.Worksheets("Sheet1")
    Set wsDest = wbDest.Worksheets("Sheet1")
    wsDest.Range("H5") = wsSource.Range("H5")
    wsDest.Range("N3") = wsSource.Range("N3")
    wsSource.Range("B12:Q21").Copy Destination:=wsDest.Range("B12")
    
    If Len(Dir(strFile)) > 0 Then Kill strFile
    wbDest.SaveAs strFile
sExit:
    On Error Resume Next
    Set wsDest = Nothing
    Set wsSource = Nothing
    wbDest.Close Savechanges:=False
    Set wbDest = Nothing
    Set wbSource = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "sExportSelectedData", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

You can either copy and paste the piece of code that sets the source/destination worksheets and does the copying for the other two worksheets, or else you could create an array of worksheet names and loop that.

Regards,

Macros for copying data from a workbook with multiple sheet to another workbook with multiple sheet

1 Answers1