VBA code to filter a specific column

[xxbc320xx]

I have a large workbook that routinely pull a sheet from and email. I have a code to email but I need to set up a filter on a specific column before emailing. This column will change each week BUT will always be the 9th non-hidden column. Is there a way I create a line of code that will filter this column?

 

[ranman256]

sub FilterMyList()
dim rng as range

range("A1").select
set rng= ActiveSheet.UsedRange
Selection.AutoFilter
rng.AutoFilter Field:=9, Criteria1:="Overdue"
end sub

 

[xxbc320xx]

The range "A1" will change time depending on what criteria. For example. One time it may be Column I another it may be Column HS. It will always be the 9th active column in the sheet.
Maybe I am misreading your code.

sub FilterMyList()
dim rng as range

range("A1").select
set rng= ActiveSheet.UsedRange
Selection.AutoFilter
rng.AutoFilter Field:=9, Criteria1:="Overdue"
end sub

 

[ranman256]

ignore A1, the filter part uses col 9.

 

[xxbc320xx]

I am stuck.
I tried adding the rng autofilter into my code and keep getting an error. I am trying to have all this run in the backround.
I have a code that takes the page on the existing sheet, copies all the data (much of it is results of vlookups and calculations), pasts to another sheet as values only, opens outlook, opens a new message, attaches this new sheet, then deletes the temp sheet.
If I filter by greater than 0 and then run the code I get an error with the copy/paste area not matching. If I run without filtering it is fine. I don't mind not filtering the main copy, but the emailed copy needs to be filtered.
Here is the part giving me trouble.

With Destwb.sheets(1).UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
            .rng.AutoFilter Field:=9, Criteria1:=">0"
        End With
       Application.CutCopyMode = False

 

[xxbc320xx]

Also
I opened a new workbook to just test this in general and it always filters the same column, regardless of whether or not it is hidden. I need to filer the 9th UNHIDDEN column.