Copy and Paste with VBA if a certain condition is met

[lor_kev]

Greetings all, I am updating an estimating spreadsheet that I wrote probably 15 to 20 years ago. I wrote a macro back then that transferred information from the cost part of the sheet to the estimate sheet below, but it was in the old XLA format and I can no longer save it or make changes to it, it still works but I cannot update it. I started playing around with the VBA to write this macro and everything works, but I am stuck on how to continue the loop to repeat the operation. I have been googling my asws off with no luck, so looking for help from people much smarter than I.

Here is a screen grab of my worksheet https://1drv.ms/u/s!ArDeb5a0pUjjgvcwHmb0JGMRt_9fbQ. When the cell in column a matches cell C4, i need to copy and paste data from several cells in the cost section at the top. The macro I wrote copies the information for the first cell it encounters, row 24, but it does not repeat the function in Row 32. Here is what I have written for my macro:

Sub RectangleRoundedCorners1_Click()


Range("a1").Select


For i = 1 To 400 'select 400 cells


ActiveCell.Offset(1, 0).Select 'drop one row and select again


If ActiveCell.Value = Range("c4").Value Then
Range("o13").Copy
ActiveCell.Offset(0, 8).PasteSpecial xlPasteValuesAndNumberFormats
Range("o12").Copy
ActiveCell.Offset(0, 1).PasteSpecial xlPasteValuesAndNumberFormats
Range("m5").Copy
ActiveCell.Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
Range("m6").Copy
ActiveCell.Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
Range("m7").Copy
ActiveCell.Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
Range("m8").Copy
ActiveCell.Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
Range("m9").Copy
ActiveCell.Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
Range("o15").Copy
ActiveCell.Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
Range("o12").Copy
ActiveCell.Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
End If


Next i


Range("c5").Select 'return home


End Sub

I assume I am missing something easy here or my syntax is wrong at the start, any help would be greatly appreciated.

 

[Fluff]

What cells from the estimate, should go to which columns in the quote?
You're code & image don't match up.

 

[DanteAmor]

Try this
Change "X" by the right column

Sub Copy_Data()
  Dim i As Long
  For i = 22 To Range("A" & Rows.Count).End(xlUp).Row
    If Cells(i, "A").Value = Range("C4").Value Then
      Range("O13").Copy Cells(i, "I")
      Range("O12").Copy Cells(i, "M")
      Range("M5").Copy Cells(i, "[COLOR=#ff0000][B]X[/B][/COLOR]")
      Range("M6").Copy Cells(i, "[B]X[/B]")
      Range("M7").Copy Cells(i, "[B]X[/B]")
      Range("M8").Copy Cells(i, "[B]X[/B]")
      Range("M9").Copy Cells(i, "[B]X[/B]")
      Range("O15").Copy Cells(i, "[B]X[/B]")
[COLOR=#0000ff]      Range("O12").Copy Cells(i, "[B]X[/B]") 'This source cell is duplicated[/COLOR]
    End If
  Next i
  MsgBox "End"
End Sub

 

[lor_kev]

Thank you Dante, this works but I need to paste special, just values and formats. This is pasting the formula within the target cell. This is what I have with the x values filled in:
Dim i As Long
For i = 22 To Range("a" & Rows.Count).End(xlUp).row
If Cells(i, "a").Value = Range("c4").Value Then
Range("o13").Copy Cells(i, "i")
Range("o12").Copy Cells(i, "j")
Range("m5").Copy Cells(i, "p")
Range("m6").Copy Cells(i, "r")
Range("m7").Copy Cells(i, "t")
Range("m8").Copy Cells(i, "v")
Range("m9").Copy Cells(i, "x")
Range("o15").Copy Cells(i, "z")
Range("o12").Copy Cells(i, "ab")
End If
Next i
Range("c5").Select 'return home
End Sub

Thank you for your help and the quick response. Kevin

 

[Fluff]

Simply swap the copy lines around like

      Cells(i, "I").Value = Range("O13").Value
      Cells(i, "J").Value = Range("O12").Value

 

[lor_kev]

Thats got it, thank you so much. Have a great day

 

[DanteAmor]

Try this

Previously put the format in the columns and just pass the value

Sub Copy_Data()
  Dim i As Long
  For i = 22 To Range("A" & Rows.Count).End(xlUp).Row
    If Cells(i, "A").Value = Range("C4").Value Then
      Cells(i, "i") = Range("o13")
      Cells(i, "j") = Range("o12")
      Cells(i, "p") = Range("m5")
      Cells(i, "r") = Range("m6")
      Cells(i, "t") = Range("m7")
      Cells(i, "v") = Range("m8")
      Cells(i, "x") = Range("m9")
      Cells(i, "z") = Range("o15")
      Cells(i, "ab") = Range("o12")
    End If
  Next i
  MsgBox "End"
End Sub

Or If the format can change, then repeat the following for each line

      Range("o13").Copy
      Cells(i, "i").PasteSpecial xlPasteValuesAndNumberFormats

 

[Fluff]

Glad to help & thanks for the feedback

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.