VBA/Macro Help: Copy and Paste x rows above, then find and replace, repeat

[felix.dwa]

I'm wondering how to build a macro which is essentially a structured copy and paste combined with a find and replace function.


I've attached an excel file with three columns. Basically I want to copy x rows from the original/source. I then paste it below and replace column B with something else. I then copy the same original source rows and paste it below and replace it with something else again.


Then I do the same but with column C.


Is this something that's possible? I would love some guidance. Please kindly advise.

[mancubus]

because your table in "Outcome" was the table itself and 8 times repeat of it in "Original" i had to guess what new values in col B and C would be.

be more specific and care to provide the actual desired output when posting your requirement and workbook to the forum.

i assume all new values in B and C are the same for a single paste operation but change one after another.

start with the below code... modify and improve it as per your requirement.

Option Base 1

Sub vbax_57643_copy_paste_n_times_replace_repeat()

    Dim orig, NewB, NewC
    Dim i As Long, j As Long
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    NewB = Array("B1", "B2", "B3", "B4", "B5", "B6", "B7", "B8")
    NewC = Array("C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8")
    
    With Worksheets("Original")
        With .Cells(1).CurrentRegion
            orig = .Offset(1).Resize(.Rows.Count - 1).Value
        End With
        
        For i = LBound(NewB) To UBound(NewB)
            For j = LBound(orig) To UBound(orig)
                orig(j, 2) = NewB(i)
                orig(j, 3) = NewC(i)
            Next j
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(orig, 1), UBound(orig, 2)).Value = orig
        Next i
    End With

End Sub