Copy Data to Row 5 VBA

[billandrew]

Good afternoon

I am trying to copy data from worksheet "Project" column G "Over" to inserted sheet "Over" to row 5 of the new sheet. Code I am using is copying the header row (row 4) and breaking at that point.

Not sure what is going wrong.


Sub Project()


Dim i As Long
Dim lr As Long
Dim lr2 As Long
Dim result As String

Sheets.Add after:=Worksheets("Project")
ActiveSheet.Name = "Over"
Sheets("Project").Activate



lr = Sheets("Project").Cells(Rows.Count, "G").Row


For i = 5 To lr

If Sheets("Project").Cells(i, "G").Value = "Over" Then
result = Cells(i, "G").Value


Sheets("Project").Rows(4).Copy Destination:=Sheets("Over").Rows(4)


lr2 = Sheets(result).Cells(Rows.Count, "G").End(xlUp).Row + 5

Rows(i).Copy desination:=Sheets(result).Rows(lr2)



End If

Next i


End Sub

 

[Fluff]

Untested but try

Sub Project()


    Dim i As Long
    Dim lr As Long
    Dim lr2 As Long
    Dim result As String
    
    Sheets.Add(after:=Worksheets("Project")).Name = "Over"
    With Sheets("Project")
        .Rows(4).Copy Destination:=Sheets("Over").Rows(4)
        lr = .Cells(Rows.Count, "G").End(xlUp).Row
        For i = 5 To lr
            If .Cells(i, "G").Value = "Over" Then
                result = Cells(i, "G").Value
                lr2 = Sheets("Over").Cells(Rows.Count, "G").End(xlUp).Row + 5
                .Rows(i).Copy destination:=Sheets("Over").Rows(lr2)
            End If
        Next i
    End With


End Sub

Cheers Mark

 

[MARK858]

Hi Fluff missing a "t" in Destination...

.Rows(i).Copy [COLOR="#FF0000"]desination[/COLOR]:=Sheets("Over").Rows(lr2)

Not sure if you have enough time to edit.

 

[billandrew]

Thanks Fluff

Works perfectly.

Not sure what i was doing wrong with my code.

 

[billandrew]

I didn't realize the missing "t" was directed for my code.

Thanks again both of you

 

[Fluff]

Thanks Fluff

Works perfectly.

Glad to help & thanks for the feedback

Not sure what i was doing wrong with my code.

The only actual problem was the missing t from destination.

However, with this line you were setting lr to 1048576

lr = Sheets("Project").Cells(Rows.Count, "G").Row

And then here you were also copying your header row every time Col G said Over

If Sheets("Project").Cells(i, "G").Value = "Over" Then
result = Cells(i, "G").Value


Sheets("Project").Rows(4).Copy Destination:=Sheets("Over").Rows(4)

 

[Fluff]

I didn't realize the missing "t" was directed for my code.

Thanks again both of you

It was infact aimed at me & I just managed to edit my code before time ran out.

 

[billandrew]

Thank you appreciate the explanations

 

[Fluff]

No problem