My goal is to take an excel document with variable row size, copy it and then paste it onto the bottom row of a new document.

Longer story, I need to take monthly sales reports and stack them into a larger excel file. Each month we make a variable number of sales. I need to aggregate all of these months together so we can process them.

I have some code that I thought worked below. It was able to move variable rows within different work sheets, but could not do the same for different work books.

Private Sub MoveRowToEndOfTable()
Dim LastRow As Long
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row
Sheets(1).Range("A2:A" & LastRow, "G2:G" & LastRow).Copy
Workbooks("BRN report Aggregator.xlsx").Worksheets("New shares EOM").Range("a6000").End(xlUp).Offset(1, 0).Cells.Insert
End Sub

1 Answers

0
Vasily On

I guess that your workbook is closed, check it before paste values (if workbook is closed ~> open it) :

Private Sub MoveRowToEndOfTable()
   Dim LastRow As Long
   LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Sheets(1).Range("A2:A" & LastRow, "G2:G" & LastRow).Copy
   Dim wb As Workbook, wb_target As Workbook
   'check if workbook is open already
   For Each wb In Workbooks
       If wb.Name = "BRN report Aggregator.xlsx" Then
           Set wb_target = Workbooks("BRN report Aggregator.xlsx")
               Exit For
       End If
   Next wb
   'if not then open it
   If wb_target Is Nothing Then
       Set wb = Workbooks.Open("Path_to_file/BRN report Aggregator.xlsx")
   End If
   wb.Worksheets("New shares EOM").Range("a6000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll 'or xlPasteValues  --depends on your needs
   wb.Close True 'save and close if required
End Sub