Offset not working correctly

[Francois084]

Hi

I'm having trouble with my offset
I used my previous code and changed it so that it look for a specific word and search over multiple worksheets
It does find the word and copies it over to the new sheet.
but when it find the 2nd one it overwrites the previous line on the new sheet

Thank you for helping

Sub OtherSearchString()

Dim iIndex As Integer
Dim ws As Excel.Worksheet
Dim lr As Long
Dim r As Long
Dim C As Range
Dim Target As Worksheet
b = 0
Set Target = ActiveWorkbook.Worksheets("Supp or Dept")

    For iIndex = 1 To ActiveWorkbook.Worksheets.Count
        Set ws = Worksheets(iIndex)
        ws.Activate
        '   Find last row in column E on Source sheet
    lr = ws.Cells(Rows.Count, "C").End(xlUp).Row

'   Loop through each row in column E backwards
    For r = lr To 1 Step -1
'       Build "c" range
        Set C = ws.Range("C" & r)
'       Proceed with rest of your code
        If C Like "*SUPP*" Then
            C.EntireRow.copy
            Target.Range("A" & Rows.Count).End(xlDown).Offset(1).PasteSpecial
            C.EntireRow.Delete
        ElseIf C Like "*DEPT*" Then
            C.EntireRow.copy
            b = Target.Range("A" & Rows.Count).End(xlUp).Offset(3).PasteSpecial
            b = b + 1
            C.EntireRow.Delete
            
        End If
    Next r
Next iIndex
End Sub

 

[mumps]

It works properly for me. This is what I get:

Book4
	A	B	C
1	A	B	C
2			SUPPLIER 1
3			SUPPLIER 2
4			SUPPLIER 3
5			SUPPLIER 4
6			SUPPLIER 1
7			SUPPLIER 2
8			SUPPLIER 3
9			SUPPLIER 4
Supp or Dept

Please put headers in row 1 of both source sheets. Also, the macro I suggested avoids using a loop which is very slow.

 

[mumps]

You are using 2 different macros which is not necessary. Everything can be done with one macro. Are all your sheets in the same workbook? Could you upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Francois084]

You are using 2 different macros which is not necessary. Everything can be done with one macro. Are all your sheets in the same workbook? Could you upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

I only started with VBA this week.
Still learning. I will try and make 1 Sub form both

Thank you