Excel VBA copy worksheet to existing sheet in unopened workbook

Question

VBA novice here.

I recorded the included code to copy a worksheet from the source workbook and paste it into a sheet of another open workbook.

It works but what I was attempting to do is; trigger macro copy worksheet open destination workbook paste data close origin workbook end on destination workbook but different worksheet.

Sub Button5_Click()
'export data to template
Sheets("DataSet").Select 'sheet that I want to copy and paste (resides in macro workbook)
Cells.Select
Selection.Copy
Windows("Missing Data Template (concept1).xlsx").Activate 'destination workbook (no macros)
Sheets("Report").Select 'destination worksheet
Cells.Select
ActiveSheet.Paste

End Sub

Thanks for some education!

JNevill JNevill · Accepted Answer · 2018-04-11T17:20:46

I believe you will have to open the other workbook, paste, and then close:

Sub Button5_Click()

    'a variable to hold our other workbook
    Dim otherwb as Workbook

    'open other workbook assign it to variable
    Set otherwb = Application.Workbooks.Open("Missing Data Template (concept1).xlsx")

    'Copypasta
    Sheets("DataSet").Cells.Copy Destination:=otherwb.Sheets("Report").Range("A1")

    'save and Close other workbook         
    otherwb.Close(true)   

End Sub

You'll notice, too, that I've done away with all the .Select and .Active type code. It's really unnecessary to .Select stuff in VBA since Selecting is something a human does. The code just needs to know what to get and where to put it. Touching with .select or .activate is really only used sometimes at the end to set the cursor/selection at a specific point for the human that is running the code (say if you wanted to insure that once this runs, the Sheets("Dataset").Range("A1") cell was selected.

Excel VBA copy worksheet to existing sheet in unopened workbook

1 Answers