VBA Entire Row PasteSpecial Values multiple errors

[Rottimad]

Hi,

I have set up the below to copy any rows which contain a matching PO value from one sheet and put them into another sheet in.

Sub InpInvExtSbc()
Dim oPO As String
Dim LR As Long, i As Long
oPO = Sheets("Input Invoice").Range("L9") & "-" & Range("L20")
With Sheets("External Subcontract")

    LR = .Range("AT" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If .Range("AT" & i).Value = oPO Then
        Rows(i).Copy
        Sheets("Input Invoice").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial x1PasteValues
    End If
    Next i
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Every time I attempt to run I get an error message stating "Variable not defined" which then highlights "x1PasteValues". Not sure of what the issue was I set this as a variable but now receive the "PasteSpecial method of Range class failed".

When I take this back to basics and simply use

.Range("AT" & i).Value = oPO Then Rows(i).Copy Destination:=Sheets("Input Invoice").Range("A" & Rows.Count).End(xlUp).Offset(1)

The macro works but the formatting issue still needs addressing.

Any assistance would be great. I've already tried using the basic copy then simply pasting the formatting from another part of the table but for some reason the Macro doesn't function then either.

 

[vcoolio]

Hello Rottimad,

I think, just maybe, that instead of "xlPasteValues" you have "x1PasteValues". Its any easy mistake to make. It looks like a "1" (one) not an "l" (el).

Maybe.....

Cheerio,
vcoolio.

 

[JackDanIce]

You may find applying a filter to AT1 faster than looping:

Sub InpInvExtSbc()

Dim oPO     As String
Dim LR      As Long

With Sheets("Input Invoice")
    oPO = .Range("L9").Value & "-" & .Range("L20").Value
End With

With Sheets("External Subcontract")

    If .AutoFilterMode Then .AutoFilterMode = False
    LR = .Range("AT" & Rows.Count).End(xlUp).Row
    
    With .Range("A1:AT1").Resize(LR)
        .AutoFilter
        .AutoFilter Field:=Range("AT1").Column, Criteria1:=oPO
        .Offset(1).Resize(LR - 1).SpecialCells(xlCellTypeVisible).Copy
    End With

    .AutoFilterMode = False

End With

With Sheets("Input Invoice")

    LR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    .Range("A" & LR).PasteSpecial xlPasteValues

End With
        

Application.Calculation = xlCalculationAutomatic
    
End Sub

 

[Rottimad]

You may find applying a filter to AT1 faster than looping:

Sub InpInvExtSbc()

Dim oPO     As String
Dim LR      As Long

With Sheets("Input Invoice")
    oPO = .Range("L9").Value & "-" & .Range("L20").Value
End With

With Sheets("External Subcontract")

    If .AutoFilterMode Then .AutoFilterMode = False
    LR = .Range("AT" & Rows.Count).End(xlUp).Row
    
    With .Range("A1:AT1").Resize(LR)
        .AutoFilter
        .AutoFilter Field:=Range("AT1").Column, Criteria1:=oPO
        .Offset(1).Resize(LR - 1).SpecialCells(xlCellTypeVisible).Copy
    End With

    .AutoFilterMode = False

End With

With Sheets("Input Invoice")

    LR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    .Range("A" & LR).PasteSpecial xlPasteValues

End With
        

Application.Calculation = xlCalculationAutomatic
    
End Sub

Jack, thanks for this however a debug screen keeps appearing stating no cells could be found (regardless of the value I set for "oPo" in Range L9 & L20). When I go and manually look for them in "External Subcontract" they do appear.

vcoolio - you were right I did put a number by mistake, it was unfortunately irrelevant as I must have made a mistake somewhere else in the code as it doesn't appear to find anything either (though at least it runs through without error).

Any suggestions? I've tried converting oPo into a single value and have the cells in "External Subcontract" as values not formulas but still I'm drawing blanks.

 

[JackDanIce]

This part of the code:

With Sheets("Input Invoice")
  oPO = .Range("L9").Value & "-" & .Range("L20").Value
End With

Assumes you meant cells L9 and L20, both on sheet Input Invoice. I didn't realise they are both in External Subcontract.

Try this instead:

Sub InpInvExtSbc()

Dim oPO     As String
Dim LR      As Long


With Sheets("External Subcontract")

    If .AutoFilterMode Then .AutoFilterMode = False
    LR = .Range("AT" & Rows.Count).End(xlUp).Row
    
    oPO = .Range("L9").Value & "-" & .Range("L20").Value
    
    With .Range("A1:AT1").Resize(LR)
        .AutoFilter
        .AutoFilter Field:=Range("AT1").Column, Criteria1:=oPO
        .Offset(1).Resize(LR - 1).SpecialCells(xlCellTypeVisible).Copy
    End With

    .AutoFilterMode = False

End With

With Sheets("Input Invoice")

    LR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    .Range("A" & LR).PasteSpecial xlPasteValues

End With
        

Application.Calculation = xlCalculationAutomatic
    
End Sub

 

[Rottimad]

JackDanICe

Apologies, you were absolutely spot on. I had to make some changes to my data in order to get the correct result but it worked in the end. Thanks for your fix.

Thanks both for your help, head scratching had left me bald!

*Edit - Jack, your first post was correct (as was the location of L9 and L20). Error on my part since resolved.

 

[JackDanIce]

You're welcome and thanks for the feedback.