Help with some code

[tcfreer]

Hi All

I'm currently working on some vba and encountering a issue that I can't find a answer or solution for

I have within a workbook several tabs

The tabs in Questions are Open POs and PO Orders

My code is as follows

Sub CopyData()
Dim LR As Long, ALR As Long
Dim MSht As String, DSht As String
MSht = "Open POs"
DSht = "PO Orders"
LR = Sheets(MSht).Range("A" & Rows.Count).End(xlUp).Row
ALR = Sheets(DSht).Range("I" & Rows.Count).End(xlUp).Row + 1
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'filter for dropout data
Sheets(MSht).Activate
    'Cells.AutoFilter
    Range.AutoFilter Field:=4, Criteria1:=Worksheets("PO Orders").Range("J1").Value
    Range("A2:G" & LR).SpecialCells(xlCellTypeVisible).Select
    Range("A2:G" & LR).Copy
    Sheets(DSht).Activate
    Cells(ALR, 1).Activate
    ActiveSheet.Paste
    Sheets(MSht).Activate
    Cells.AutoFilter
    Sheets(DSht).Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

I want the above to take a value from cell J1 on the PO Orders, Filter Open POs on column D(Field 4), then copy that data into the PO Orders tab, using cell I4 as the equivalent of A1

The line that it keeps stopping on is this one

Range.AutoFilter Field:=4, Criteria1:=Worksheets("PO Orders").Range("J1").Value

My error message is "Complie error: Argument not optional"

Any help would be gratefully received

TC

 

[ajamess]

At first glance I believe you need to have a range before your .autofilter.

Range("Range here").autofilter Field:=4, Criteria1:=Worksheets("PO Orders").Range("J1").Value

 

[Fluff]

You haven't specified the range. Try

    Range("A1:G" & LR).AutoFilter Field:=4, Criteria1:=Worksheets(DSht).Range("J1").Value

 

[Fluff]

You also state

using cell I4 as the equivalent of A1

But you are pasting into column A not I.
FYI your code can be simplified like

Sub CopyData()
    
    Dim LR As Long, ALR As Long
    Dim MSht As Worksheet, DSht As Worksheet
    
    Set MSht = Sheets("Open POs")
    Set DSht = Sheets("PO Orders")
    LR = MSht.Range("A" & Rows.Count).End(xlUp).Row
    ALR = DSht.Range("I" & Rows.Count).End(xlUp).Row + 1
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    'filter for dropout data
    With Sheets(MSht)
        .Range("A1:G" & LR).AutoFilter Field:=4, Criteria1:=DSht.Range("J1").Value
        On Error Resume Next
        .Range("A2:G" & LR).SpecialCells(xlCellTypeVisible).Copy DSht.Range("[COLOR=#ff0000]I[/COLOR]" & ALR)
        On Error GoTo 0
        .Cells.AutoFilter
        DSht.Select
    End With
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

This assumes you want to past the data starting in Col I (if not change the I in red to whichever column you want)
I've also added an error handler incase there are no visible cells to copy

 

[tcfreer]

Brilliant - Thanks both, that worked fine

I noe need to fix the copy

This should pf pasted in from I4, but instead pasted in from A4

Any ideas based on the OP?

 

[tcfreer]

Hi Fluff

Sorry, posted as you posted your amended version

I've tried it and it errors on the "with Sheets(Msht)" line as a type mismatch

 

[Fluff]

Apologies, missed that one, it should be

With MSht