finding first blank row

[peter renton]

Hi All

I have attached a small wb, this mirrors certain cells from sheet1 to sheet 2 but because it jst mirrors it copies them into the
same row on sheet two. I need the data to update if it is changed in sheet one but would like it to be entered into the first
row with column a blank.
the reason being is that the full wb has an auto row deletion depending on a given date which would then delete a row in sheet one and because its
mirrored would leave blank rows in sheet 2.
Idealy if the data could be copied over and be amended if changed but not be deleted if sheet 1 is cleared that would be spot on.

Sheet 2 will be a summary profit/loss sheet

If there is a better ideal rather than mirror data i am open to any sugestions

thank you in anticipation

Peter

[peter renton]

hi
the reg number would be a uniqe identifiyer could this be used to locate the row to update?

peter

[Admin]

Hi

See if this helps you.

Sub UpdateSheet2()
    
    Dim rngSource       As Range
    Dim RegNum          As String
    Dim rngRegNum       As Range
    Dim varFound, i     As Long
    
    
    With Sheet1
        Set rngSource = .Range("a3:h" & .Range("b" & .Rows.Count).End(xlUp).Row)
    End With
    
    With Sheet2
        Set rngRegNum = .Range("b1:b" & .Range("b" & .Rows.Count).End(xlUp).Row)
    End With
    
    '//loop through all the reg. #
    For i = 1 To rngSource.Rows.Count
        
        '//reg. # in 2nd column
        RegNum = rngSource.Cells(i, "b")
        
        '//look for the reg # in sheet2
        varFound = Application.Match(RegNum, rngRegNum, 0)
        
        '//if found, update the data
        If Not IsError(varFound) Then
            
            '//found cell
            With rngRegNum.Cells(varFound, 1)
                .Offset(, -1).Value2 = rngSource.Cells(i, 1).Value2   'Customers Name
                .Offset(, 1).Value2 = rngSource.Cells(i, 5).Value2    '1ST serice costs
                .Offset(, 2).Value2 = rngSource.Cells(i, 6).Value2    '2nd service costs
                .Offset(, 3).Value2 = rngSource.Cells(i, 7).Value2    'Bugeted costs
                .Offset(, 4).Value2 = rngSource.Cells(i, 8).Value2    'End Date
            End With
        End If
    Next
    
End Sub