Auto Filter in VBA

[debschofield]

HI - I wonder if someone can tell me where I have gone wrong? Thank you



Sub TestMacro()

' written to test look up of data in raw data spreadsheet and copy/paste to another sheet
' I want to select the data in column AA and filter on only items that map to type 01. Income from the VLOOKUP table
' I then want to copy/paste only the Income items from the GL Raw data worksheet (columns A to U) to the Income Data worksheet
' I think the best approach is to autofilter on column AA which is field 27



With ThisWorkbook.Sheets("GL Raw Data").Select

. I've checked the worksheet name and this is correct
.AutoFilter field:=27, Criteria1:="1. Income"
End With


Sheets("GL Raw Data").Select
Copyvalues.Range ("A:U")





'I think the below command will correctly paste the data

Sheets("Income Data").Select


'***Insert Values

WKS.Range("A1").PasteSpecial xlPasteValues


'Then we need to refresh the Income Pivot, close the income data worksheet and return to the header worksheet



End Sub

 

[MARK858]

First of all it is very hard to read your code as you haven't put it in code tags and have tried (and failed) to insert an image into your code. Please post your code (in code tags) without trying to put an image in the code.

But to get you started you shouldn't have .Select at the end of your With statement i.e.

With ThisWorkbook.Sheets("GL Raw Data").Select

should just be

With ThisWorkbook.Sheets("GL Raw Data")

I also can't see anywhere that you have defined WKS or Copyvalues.
I think I could take a guess at everything but I will wait until you post back

If you don't know how to insert code tags just paste your code in the thread, select it and then click the # icon.

 

[debschofield]

Thank you

Here is the copy of my code:

Sub TestMacro()


' written to test look up of data in raw data spreadsheet and copy/paste to another sheet




' I want to select the data in column AA and filter on only items that map to type 01. Income from the VLOOKUP table
' I then want to copy/paste only the Income items from the GL Raw data worksheet (columns A to U) to the Income Data worksheet
' I think the best approach is to autofilter on column AA which is field 27






With ThisWorkbook.Sheets("GL Raw Data").Select
.AutoFilter field:=27, Criteria1:="1. Income"
End With




Sheets("GL Raw Data").Select
Copyvalues.Range ("A:U")










'I think the below command will correctly paste the data


Sheets("Income Data").Select



'***Insert Values

WKS.Range("A1").PasteSpecial xlPasteValues


'Then we need to refresh the Income Pivot, close the income data worksheet and return to the header worksheet




End Sub

I have noted the incorrect use of .select Thanks

 

[MARK858]

Just reposted for my own use as the OP still didn't use code tags or defined their variables

Sub TestMacro()


' written to test look up of data in raw data spreadsheet and copy/paste to another sheet




' I want to select the data in column AA and filter on only items that map to type 01. Income from the VLOOKUP table
' I then want to copy/paste only the Income items from the GL Raw data worksheet (columns A to U) to the Income Data worksheet
' I think the best approach is to autofilter on column AA which is field 27






With ThisWorkbook.Sheets("GL Raw Data").Select
.AutoFilter field:=27, Criteria1:="1. Income"
End With




Sheets("GL Raw Data").Select
Copyvalues.Range ("A:U")

'I think the below command will correctly paste the data


Sheets("Income Data").Select


'***Insert Values

WKS.Range("A1").PasteSpecial xlPasteValues


'Then we need to refresh the Income Pivot, close the income data worksheet and return to the header worksheet

End Sub

 

[MARK858]

Maybe...

Sub Filterit()
    Dim WKS As Worksheet
    Set WKS = Sheets("Income Data")
    With Sheets("GL Raw Data").Range("A1:AA" & Sheets("GL Raw Data").Range("AA" & Rows.Count).End(xlUp).Row)
        .AutoFilter Field:=27, Criteria1:="1. Income"
        
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1, .Columns.Count -6).SpecialCells(12).Copy
        WKS.Range("A1").PasteSpecial xlPasteValues
        On Error GoTo 0
        
        .AutoFilter
    
    End With
End Sub

 

[debschofield]

Perfect

thank you for your help