use a last row calculation from a destination workbook as a row value range in a source workbook

Question

Excuse my VBA ignorance.. I'm trying to automate an export of selected columns from a source workbook, which is populated continuously from a web-based source. The destination workbook is needed for a record-keeping process. My problem is that a simple copy/paste will write over the cells in the destination workbook. I've got code to calculate the last row used in my destination sheet, which I'm calling lastrow. I'd like to use the lastrow as the row value so that I can copy from the source workbook beginning at the lastrow calculated from my destination workbook. The goal is to continuously add rows to my destination sheet from my source sheet, without writing over the existing rows of data.

Sub johnson()
    Dim wb As Workbook, wbTemp As Workbook
    Dim ws As Worksheet, wsTemp As Worksheet
    Dim lastrow As Long

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")

    Set wbTemp = Workbooks.Open("F:\Projects\Ballot Reconciliation\DRAFT 2014     
    Reconciliation spreadsheet.xlsx")
    Set wsTemp = wbTemp.Sheets("Sheet1")

    'calculate last row having been used in the destination worksheet

    lastrow = wsTemp.Range("B" & Rows.Count).End(xlUp).Row + 1

    'use lastrow as the input row value for the copy out of the source worksheet.

    ws.Range("A(lastrow):A5000").Copy wsTemp.Range("B" & lastrow)

    Application.CutCopyMode = False

    wbTemp.Close savechanges:=True
    Set wb = Nothing: Set wbTemp = Nothing
    Set ws = Nothing: Set wsTemp = Nothing

End Sub

Dan Wagner Dan Wagner · Accepted Answer · 2014-05-23T14:47:15

You'll need to calculate the last row on the record-keeping sheet as well and set up your .Copy action using that as the destination (rather than the last row from the source sheet):

Dim LastTargetRow As Long

'... do stuff

With wsTemp
    LastTargetRow = .Range("B" & .Rows.Count).End(xlUp).Row + 1
End With

'... do stuff

Then you can simply copy to wsTemp.Range("B" & LastTargetRow).

Something nit-picky here, but potentially a saver of headaches further down the line... What do you think about adjusting your variable names to better indicate what they are up to? I might pick Source instead of ws to describe the source data sheet, DestBook for the destination workbook and Dest for the destination sheet...

use a last row calculation from a destination workbook as a row value range in a source workbook

2 Answers