Hi,

I am new to this forum and would need some help from you guys. I have a spreadsheet with a bunch of Data and want to saperate the Data into different Sheets due to the information in a certain row. I had to create the column and therefore I wrote the following macro.

Sub Fill()
    Dim Finalrow As Long
    Dim i As Long
    Dim var As String
    Dim ord As String
    Dim pos As String
    Dim row As Integer
    
    Finalrow = Sheets("Data").Range("A1").End(xlDown).row
    Cells(1, 15) = Finalrow
For i = 2 To Finalrow
    If Cells(i, 5) = "Information" Then
    var = Cells(i, 7).Value
    ord = Cells(i, 2).Value
    pos = Cells(i, 4).Value
    End If
    
    If Cells(i, 2) = ord And Cells(i, 4) = pos Then
    Cells(i, 11) = var
    End If
    
Next i
What this code is doing, is basically looking for a certain word (information) in column 5 and then copying it in every row of that column which has the same ord & pos as the row with the word information in it. This part of the Macro works fine. But the next part I wrote is not really working:
For i = 2 To Finalrow
    If Cells(i, 11) = "Car" Then
    Range(Cells(i, 1), Cells(i, 10)).Copy
    Sheets("Car").Range("A1040000").End(xlUp).Offset(1, 0).Paste
    End If
Next i
    
End Sub
What this should basically do is looking for the word Car in column 11 and then copying the whole row into a new Sheet in that Spreadsheet called Car. But somehow this part of the macro is not working. It would be great if somebody could give me some advice.
I want to do this searching for a certain word and copying into a certain sheet with 7 different search words. Does anybody have a suggestion how to do this the best way, As I believe just copying and editing that text 7 times in that macro could make it very inefficient and maybe will cause it to break down (Especially as it will have to go through a lot of Data. I thought about using a table saying which search word links to which sheet or something like that to make the macro more stable.
Also any suggestions to make the macro more stable would be appreciated, as I just started creating Macros, and I am willing to learn :-)

Thank you very much ;-)