Code Please That Could Save Me Hours

[Dazzawm]

I need a code that will look at any data that is in column AG and copy it 1 row up and 5 columns to the left (AB) but only if the destination cell has NYA in it. Is this possible please? Thanks

 

[Jon von der Heyden]

Sub TaggaTaggaTagga()
    With ActiveSheet
        .AutoFilterMode = False
        With Intersect(.Columns("AB"), .UsedRange)
            .AutoFilter Field:=1, Criteria1:="=NYA"
            .SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=R[1]C[5]"
            .AutoFilter
            .Value = .Value
        End With
    End With
End Sub

 

[Dazzawm]

Sub TaggaTaggaTagga()
    With ActiveSheet
        .AutoFilterMode = False
        With Intersect(.Columns("AB"), .UsedRange)
            .AutoFilter Field:=1, Criteria1:="=NYA"
            .SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=R[1]C[5]"
            .AutoFilter
            .Value = .Value
        End With
    End With
End Sub

It didnt quite work. It did copy up 1 and across 5 but it put zeros in all the other cells in AB which it shouldnt have touched. (A lot of the rows in column AG will be blank) so it only needs to copy across only the cells that have data in them.

 

[Dazzawm]

I have also noticed that is copying across to cells that have data other than NYA, when it should do nothing

 

[Jon von der Heyden]

Sub TaggaTaggaTagga()
    With ActiveSheet
        .AutoFilterMode = False
        With Intersect(.Columns("AB:AG"), .UsedRange)
            .AutoFilter Field:=1, Criteria1:="=NYA"
            .AutoFilter Field:=6, Criteria1:="<>"
            .Resize( ,1).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=R[1]C[5]"
            .AutoFilter
            .Value = .Value
        End With
    End With
End Sub

 

[Dazzawm]

It has now come up with an error of a red cross with 400 next to it.

 

[Jon von der Heyden]

Sub TaggaTaggaTagga()
    With ActiveSheet
        .AutoFilterMode = False
        With Intersect(.Columns("AB"), .UsedRange)
            .AutoFilter Field:=1, Criteria1:="=NYA"
            .SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=IF(LEN(R[1]C[5]),R[1]C[5],"")"
            .AutoFilter
            .Value = .Value
        End With
    End With
End Sub

 

[Dazzawm]

No the 400 error has come up again.

 

[Jon von der Heyden]

What is the error description?
What line is highlighted when the error occurs?
Is the sheet that this applies to the active sheet?

 

[Dazzawm]

It is just a box saying Microsoft Visual Basic with a red cross and 400 in it. No line is highlighted I just press ok and it seems to finish but nothing seems to have been done. I have got the sheet 'active' that I want the code to work on when I run the code.