Copy and paste values from a workbook into a closed workbook

Question

I'm fairly new to VBA coding, so please pardon any ignorance. How can I copy from one sheet (DIC) in workbook 1 and paste values in a sheet (Archive) in a second workbook starting from the first empty row? I don't have the second worksheet open often, so if it can be done without keeping it open, that would be preferred.

I compiled a code to get it to copy into a second sheet within the same workbook, but I'm stumped when it comes to getting it into the second workbook.

Here's the code that I have so far:

    Sub copytoarchive()

'Copy From DIC
    Sheets("DIC").Select
    Range("A4:Q4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

'Paste into Archive
    Sheets("Archive").Select
     Dim NextRow As Range
     Set NextRow = Range("A65536").End(xlUp).Offset(1, 0)
    NextRow.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

'Clear Memory
    Set NextRow = Nothing

    End Sub

I'm pretty sure you'll have to have the file open to paste values in it. You can write the VBA to open it before writing to it though and have it close it once it's done. — Alexis Olson

user3598756 user3598756 · Accepted Answer · 2016-08-10T17:31:51

avoid all that Select/Selection an refer to fully qualified ranges

try this (commented) code:

Option Explicit

Sub copytoarchive()
    Dim destSht As Worksheet

    Workbooks.Open ("C:\...\FileToCopyTo.xlsx") '<- at opening a workbook it becomes the active one
    Set destSht = ActiveWorkbook.Worksheets("Archive") '<-- set the destination worksheet in the activeworkbook

    With ThisWorkbook.Worksheets("DIC") '<--refer to your source worksheet in the workbook this macro resides in
        With .Range(.Range("A4:Q4"), .Range("A4:Q4").End(xlDown)) '<--| refer to your range whose values are to be copied
            destSht.Cells(destSht.Rows.Count, 1).End(xlUp).Offset(1).Resize(.Rows.Count, .Columns.Count).Value = .Value '<--| copy values in a equally sized range in destination worksheet starting at the first empty cell in column "A"
        End With
    End With

    destSht.Parent.Close True '<--| close the destination workbook, which is obtained as the Parent object of the destination worksheet
End Sub

just change "C:...\FileToCopyTo.xlsx" with your actual destination workbook full path

be aware that such a range as you selected may incur in an error should there be no filled rows below "A4:B4"

Copy and paste values from a workbook into a closed workbook

2 Answers