CODEWITHSUNDEEP
vbaexcelmacrosexcel-2010
Scionara
Scionara

Reputation: 7

Copy and paste values from a workbook into a closed workbook

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

Upvotes: 0

Views: 9296

Answers (2)

ASH
ASH

Reputation: 20302

You can certainly copy a range from a closed workbook.

http://www.rondebruin.nl/win/s3/win024.htm

I don't believe you can save data to a closed workbook. I can't even imagine how that would work.

Upvotes: 0

user3598756
user3598756

Reputation: 29421

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"

Upvotes: 2

Related Questions