Search code examples
excelvbams-office

Excel Array Overwrites, Need Append

All, I have been Googling and attempting, and then more Googling and attempting. I am attempting to search multiple sheets for negative values, copy the entire row(the value not the formula), and then add them all to a central location for viewing. ANY pointers would be greatly appreciated.

My Code:

Sub Button6_Click()

Set Sh1 = Sheets("Destination Sheet")

Application.ScreenUpdating = False
For Each v in Array ("Sheet1", "Sheet2", "Sheet3")
  On Error Resume Next
  Worksheets(v).Range("M10:M").AutoFilter 1, "<0"
  Worksheets(v).AutoFilter.Range.EntireRow.Copy
  Sh1.Range("A3").PasteSpecial xlPasteValuesAndNumberFormats

Next


End Sub
Solution

  • This is the typical way to append a row to an existing table:

    Sh1.Cells(rows.count, "A").End(xlUp).Offset(1,0).PasteSpecial xlPasteValuesAndNumberFormats

    Note this requires each row in the destination sheet to have value in ColA: otherwise the paste may occur in the "wrong" row.