none
VBA logging info in row in another worksheet RRS feed

  • Question

  • Hello,

    I need assistance with this code below. When I run the macro it does place the information from sheet 1 that shows 'closed' in column D into sheet 2. But when I delete those rows from Sheet 1 (they no longer need to be on sheet 1 when closed) and select 'closed' for more rows the macro will paste that information over the previous entry.

    Can you please assist with what needs to be added into this code for information to be logged without being erased in the future? For instance, if a row is closed and is deleted from sheet 1 I would still like that information to be pasted permanently on sheet 2.

    Sub better_mod()
    Dim lRow As Long
    With Sheet1
    .Range("D:D").AutoFilter 1, "Closed"
    .Columns("B:D").Hidden = True
    lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Range("A2:I" & lRow).SpecialCells(xlCellTypeVisible).Copy
    Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlValues
    .Range("D:D").AutoFilter
    .Columns("B:D").Hidden = False
    End With
    End Sub

    Tuesday, December 2, 2014 3:54 PM

All replies

  • Your code is basing the paste on column A, so if column A is not filled, then it will always overwrite existing data.

    So, choose a column that is filled, and then change

    Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlValues

    to this, if column D of Sheet2 is filled (with the necessary changes bolded)

    Sheet2.Cells(Sheet2.Rows.Count, 4).End(xlUp).Offset(1, -3).PasteSpecial xlValues


    Tuesday, December 2, 2014 4:41 PM
  • Thank you so much. That fixed it.
    Tuesday, December 2, 2014 9:13 PM