PDA

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



felix.dwa
11-04-2016, 09:56 AM
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
11-05-2016, 08:06 AM
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