none
copy the row to new workbook if column A contains a new value or at least compared to last row. RRS feed

  • Question

  • I'd appreciate it if someone can shed some light on my dilemma 

    copy the row to new workbook if column A contains a new value(at least with respect to previous row.)

    Thanks,

    jc

    Thursday, October 4, 2012 7:58 AM

Answers

  • Hi,

    First you should save your values in column A in array or save the original values in different column (for example B).

    You can use the sheet change event to check if the figures in column A are diffrent then column B or compare the values in column A to a specific array, in case value changed in column A compared to column B then the entire row is copy to a new workbook.

    Here is the code that will do the work:

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim NewBook As Workbook
    Dim SourceBook As Workbook

    Dim i As Long
    Dim iCounter As Long

    Dim TheRange As Range

    Dim FirstRow As Long
    Dim LastRow As Long

    Set SourceBook = ActiveWorkbook
    Set NewBook = Workbooks.Add

    SourceBook.Activate

    Set TheRange = Sheet1.UsedRange.Columns("a")

    FirstRow = TheRange.Row
    LastRow = FirstRow + TheRange.Rows.Count - 1


    i = 1
    For iCounter = FirstRow To LastRow
        If Cells(iCounter, "b") <> Cells(iCounter, "a") Then
            Rows(iCounter).Copy NewBook.Sheets(1).Cells(i, "a")
            i = i + 1
        End If
    Next iCounter

    End Sub


    Guy Zommer

    Thursday, October 4, 2012 9:34 AM