Paste to Worksheet that is not open

[nniedzielski]

Hello all-

I have this macro that will copy and paste data between two workbooks, however is there a way to paste into wsDest even if wsDest is not open? trying to make this as a simple of a process as possible, when i try i get an error:

Dim wsCopy As Worksheet
    Dim wsDest As Worksheet
    Dim lCopyLastRow As Long
    Dim lDestLastRow As Long

    'Set variables for copy and destination sheets
    Set wsCopy = Workbooks("XMS Trend Macro v 1.0.0.xlsm").Worksheets("Pivot")
    Set wsDest = Workbooks("XMS Trend Repository.xlsx").Worksheets("Data")
   
    'Find last used row in the copy range based on data in column A
    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
   
    'Find first blank row in the destination range based on data in column A
    'Offset property moves down 1 row
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
   
    'Copy & Paste Data
    wsCopy.Range("A2:k" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)

 

[johnnyL]

You can't paste to a closed workbook. You would have to open it, do the paste, then save/close it.

 

[nniedzielski]

ok, that makes sense, thanks