VBA Loop for copy and paste with search

[gg452]

Hi There,

Thank you in advance for taking the time to read this. I've tried to search for the answer but to no avail so I'm hoping some very kind person will be able to help put me out of my misery.

I'm trying to search for a specific term within a column of a worksheet, if that is found, execute a copy and paste (in the row above), then move and search for the next term up to 1,000 rows.

The frustrating part is that it executed perfectly once, then is giving me a 1004 error and I can't figure out why the error is occurring.

I appreciate any help I can get on this.

Sub NewMonthAlt()
    Dim i As Integer
    Dim startRange As Integer, endRange As Integer
    Dim rng As Range
    Dim searchWord As String
    Dim rngPaste As Range
    Dim startpaste As Integer
        searchWord = "XX"
    

    With Sheets("Models")

        For i = 1 To .Cells(Rows.Count, 1000).End(xlUp).Row
            If .Range("C" & i) = searchWord Then   'Here it notes the row where we first find the search word
                startRange = i
                Do Until .Range("C" & i) <> searchWord
                    i = i + 1 'Here it notes the first time it stops being that search word
                startpaste = i - 2
                Loop
                endRange = i - 1 'Backtracking by 1 because it does it once too many times
                Exit For
            End If
        Next


        'Select range to copy
        Set rng = ActiveSheet.Range("D" & startRange & ":O" & endRange)
        
        Set rngPaste = ActiveSheet.Range("D" & startpaste & ":O" & startpaste)
        
        rng.Copy

        rngPaste.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False 'Paste it to the address set


    End With
End Sub

 

[DanteAmor]

I am trying to understand your code. You copy a range and paste in another range.
But really what do you need.
If you had some data like the ones shown below, what should the macro do?
Or better yet, if you explain what data you have and what you need?

Dante Amor.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	A1	B1	XX	D1	E1	F1	G1	H1	I1	J1	K1	L1	M1	N1	O1
2	A2	B2	XX	D2	E2	F2	G2	H2	I2	J2	K2	L2	M2	N2	O2
3	A3	B3	XX	D3	E3	F3	G3	H3	I3	J3	K3	L3	M3	N3	O3
4	A4	B4		D4	E4	F4	G4	H4	I4	J4	K4	L4	M4	N4	O4
5	A5	B5		D5	E5	F5	G5	H5	I5	J5	K5	L5	M5	N5	O5
6	A6	B6		D6	E6	F6	G6	H6	I6	J6	K6	L6	M6	N6	O6
7	A7	B7	XX	D7	E7	F7	G7	H7	I7	J7	K7	L7	M7	N7	O7
8	A8	B8	XX	D8	E8	F8	G8	H8	I8	J8	K8	L8	M8	N8	O8
9	A9	B9	XX	D9	E9	F9	G9	H9	I9	J9	K9	L9	M9	N9	O9
Models