Autofilter variables in Excel 1

[shortnbald]

Hi, I want to simplify my VBA, I'm trying to use a crietia as a variable - and this is not working well (suprise!) any ideas advice appreciated, I've included the basic idea

Sub L3AnE()
Dim WBN As Workbook
Dim CTO As String

Workbooks.Open Filename:="blahblah/address/dataAsheet.xls"
Set WBN = ActiveWorkbook
Set CTO = "whateverA"
Aplication.Run "stubbie"

End Sub

stubbie runs the copy paste, adding named rages etc for local calculations of the data from the above to identified sheets with the variable criteria "whateverA" -

Selection.AutoFilter field:=3, Criteria1:=CTO

this is for 140 sheets...with different criteria for each.
Ideas??

 

[PHV]

First, you've to replace this:
Set CTO = "whateverA"
with this:
CTO = "whateverA"

Next, as you don't pass any parameter to the stubbie procedure, you should make CTO global.

Dim CTO As String
Sub L3AnE()
    Workbooks.Open Filename:="blahblah/address/dataAsheet.xls"
    CTO = "whateverA"
    Aplication.Run "stubbie"
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shortnbald]

Cheers