How do I re-write my VBA to paste a copied row over a row in a separate worksheet with a matching value?

user_18566978 0
Viewed 33

I have a VBA code-block which I want to amend very slightly. Please see the code-block I am referring to below. I will explain further.

If prev_row <> 0 And Worksheets("Block Chain").Range(change_type_column & last_row).Value = "New" Then
  For curColumn = 1 To last_col
    column_title = Worksheets("Block Chain").Cells(1, curColumn).Value
    If column_title <> "change type" Then
      If Worksheets("Block Chain").Cells(last_row, curColumn).Value <> Worksheets("Block Chain").Cells(prev_row, curColumn).Value Then
        first_empty_row = Worksheets(column_title).Cells(Rows.Count, 1).End(xlUp).Row + 1
        Worksheets("Block Chain").Range(last_row & ":" & last_row).Copy Worksheets(column_title).Range(first_empty_row & ":" & first_empty_row)
      End If
    End If
  Next curColumn
End If

The piece of code in here I am referring to is:

Worksheets("Block Chain").Range(last_row & ":" & last_row).Copy Worksheets(column_title).Range(first_empty_row & ":" & first_empty_row)

At the moment, this line copies a row from one worksheet (Block Chain) and pastes it into the last row of whichever worksheet is currently stored in the variable column_title.

Now, I want to amend this code to do the following:

  1. I want it to copy the row from Block Chain, which it already does
  2. I want it to look for the row in the column_title worksheet which has the matching value in column A
  3. Lastly, I want the code to paste the copied row over the row located in step 2

If you know how to re-write this code to do what I have specified above, I would greatly appreciate your help.

Thank you very much.

vba excel
1 Answers

The code below declares a range variable named foundCell. It then tries to bind that range variable to a cell in column A of the column_title sheet that has the same value as the cell in column A of row "last_row" from the "block chain" sheet. If the value is not found, the code stops.

Dim foundCell As Range
If prev_row <> 0 And Worksheets("Block Chain").Range(change_type_column & last_row).Value = "New" Then
  For curColumn = 1 To last_col
    column_title = Worksheets("Block Chain").Cells(1, curColumn).Value
    If column_title <> "change type" Then
      If Worksheets("Block Chain").Cells(last_row, curColumn).Value <> Worksheets("Block Chain").Cells(prev_row, curColumn).Value Then
        first_empty_row = Worksheets(column_title).Cells(Rows.Count, 1).End(xlUp).row + 1
        Set foundCell = Worksheets(column_title).Columns(1).Find(Worksheets("Block Chain").Rows(last_row).Cells(1), , , xlWhole)
        If foundCell Is Nothing Then
            Stop
            ' the value from column a in block chain worksheet was not found in column a of the column_title sheet.  Need to decide what to do
        Else
            Worksheets("Block Chain").Range(last_row & ":" & last_row).Copy foundCell
        End If
      End If
    End If
  Next curColumn
End If
user_22206650
Related