Auto Filter in VBA

debschofield

New Member
Joined
Oct 1, 2019
Messages
10
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
clip_image001.png
. 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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.

Code:
With ThisWorkbook.Sheets("GL Raw Data").Select
should just be
Code:
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
Just reposted for my own use as the OP still didn't use code tags or defined their variables

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
 
Upvote 0
Maybe...
Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top