Thread: Auto Filter processing all variables

I currently use some code in a project like this.

Sub MakePDF()Dim RegGroup As String
    RegGroup = Range("G1")
    ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=4, Criteria1:=RegGroup
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "U:\emailtemp\Weekly BMR Data " & Format(Date, "ddmmyy") & " " & RegGroup & ".PDF", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
   End Sub

RegGroup is a list of known values that cell G1 steps through (using a different bit of code thats not important.)

What i would like to do is for the filter to step through all of the variables in the column rather than a specific filter value being passed to the filter.

The values will always be a text string and not a number. I'm guessing that i need the code to first test the column for each unique value and then step though each one as the criteria.

Any ideas?

Sub snb()
 columns(7).advancedfilter xlfiltercopy,,cells(1,20),true

 with ActiveSheet.ListObjects("Table6").Range
  for each cl in columns(20).specialcells(2).offset(1).specialcells(2)
   .AutoFilter 4, cl.value
   ActiveSheet.ExportAsFixedFormat xlTypePDF, "U:\emailtemp\Weekly BMR Data " & Format(Date, "ddmmyy ") & cl.value & ".PDF"
   .autofilter
  next
 end with
End Sub

Thanks for the suggestion snb but i cannot create a list within the same sheet as done in your example.
Ideally i don't want to edit the data on/in sheet/workbook.

Ive attached and example of data. Filtering Example.xlsm

Often the task im given is to run a report (large data sets from an MIS), apply a filter, and print/PDF separate reports from the output for each of the variables in any given column.

Not sure if im now making more or less sense.

Why not ?
You can remove those data afterwards.

Sub snb()
 columns(7).advancedfilter xlfiltercopy,,cells(1,20),true

 with ActiveSheet.ListObjects("Table6").Range
  for each cl in columns(20).specialcells(2).offset(1).specialcells(2)
   .AutoFilter 4, cl.value
   ActiveSheet.ExportAsFixedFormat xlTypePDF, "U:\emailtemp\Weekly BMR Data " & Format(Date, "ddmmyy ") & cl.value & ".PDF"
   .autofilter
  next
  Activesheet.columns(20).clearcontents
 end with
End Sub

the reports will always have a variable number of columns. Although (thinking while im typing) i suppose i could use something to determine the width of the reports first and set the Offset accordingly in your example.

When i run your example i get an error on the with line "subscript out of range". Is it because i have the name ranged set to the wrong area?

yes determining the last column isn't a big deal.

Yes, it means "table6" doesn't exist.

I've checked in name manager and table6 is definitely in there with the range of data.
I've tried changing it to

 With ActiveSheet.ListObjects("$A$1:$G$20")

and no joy either.

What am i missing?

Don't use 'activesheet' but refer to the sheet, using it's name: sheets("sheetname") and if necessary make the reference to the workbook the worksheet belongs to.

thanks for your time and patience snb but i just cant get this to work with the example attached.
I'm clearly making an error but regardless of what i refference in that line i cant get it to execute as expected.

If possible could you amend the example i've attached in the previous post, your code sample is already in the module, it would be much appreciated.

snb, I've got my act together and finally got my head around it.

Sub snbpointers()
Selection.AutoFilter
 Columns(1).AdvancedFilter xlFilterCopy, , Cells(1, 20), True
  For Each cl In Columns(20).SpecialCells(2).Offset(1).SpecialCells(2)
  ActiveSheet.Range("$A$1:$G$20").AutoFilter Field:=1, Criteria1:=cl.Value
     ActiveSheet.ExportAsFixedFormat xlTypePDF, "U:\emailtemp\Weekly BMR Data " & Format(Date, "ddmmyy ") & cl.Value & ".PDF"
  Next
End Sub

Is giving me what i need. I can amend it now to make it fit any of the projects i'm working on (columns and field becoming variables to).

Again thanks for any time you spent on this it was much appreciated.

I would use this:

Sub snb()
  With Sheets("Sheet1")
    .Columns(1).AdvancedFilter xlFilterCopy, , Cells(1, 20), True
    sn=.Columns(20).SpecialCells(2).Offset(1).SpecialCells(2)
    .columns(20).clearcontents

    With .cells(1).currentregion
      For Each cl In sn
        .AutoFilter 4, cl
        .ExportAsFixedFormat xlTypePDF, "U:\emailtemp\Weekly BMR Data " & Format(Date, "ddmmyy ") & cl & ".PDF"
        .AutoFilter
      Next
    End With
  End With
End Sub

thanks again snb,

Your version seems to run quicker as well. not sure if that's just my imagination.