Creating tables using AutoFilter in Macro

[lisa.mychalishyn]

Hello,

I'm having a weird problem. I've created a Macro which uses
AutoFilter to cycle through the categories. I'd like to create a
table for each category. The macro works, in that it changes the
category and creates a chart for each, but the values remain the
same. I tried recording a couple of different ways...unselecting and
reselecting the "new" data set, but it doesn't seem to matter. The
only thing that I can think of is that I've selected the columns, not
the invdividual cells. Unfortunately, I don't know how many records
are going to be in each category, so I don't believe that I can use
relative or absolute cell references. Any ideas or suggestions would
be greatly appreciated. (I've included a sample of the code below)

Lisa

Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Auto & Truck Repair"
Columns("B:C").Select
Charts.Add
ActiveChart.ChartType = xlBarClustered
ActiveChart.SetSourceData
Source:=Sheets("Format").Range("B1:C821"), PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
"Auto & Truck Repair"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Cost to Rev %"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Auto &
Truck Repair"
End With
ActiveChart.HasLegend = False
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 1
.TickMarkSpacing = 1
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
...
ActiveChart.PlotArea.Select
Selection.Top = 32
Selection.Height = 582
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True,
LegendKey:= _
False, ShowSeriesName:=False, ShowCategoryName:=False,
ShowValue:=True, _
ShowPercentage:=False, ShowBubbleSize:=False
Sheets("Format").Select
Selection.AutoFilter Field:=1, Criteria1:="Auto Allowance"
Columns("B:C").Select
Charts.Add
ActiveChart.ChartType = xlBarClustered
ActiveChart.SetSourceData
Source:=Sheets("Format").Range("B1:C821"), PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Auto
Allowance"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Cost to Rev %"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Auto
Allowance"
End With

 

[mdupris]

Lisa,

I would guess the problem is in the "Range("B1:C821")" part of your
macro. That specifies a fixed range to plot independent of what's
showing on the screen, hence your values would never change.
What you might try doing is copying the columns after the filter is
selected and paste them to a different area, or an entirely different
worksheet. Copying an autofiltered range just copies the visible
cells, which is what I think you want to do. If you always copy the
filtered cells to the same location, e.g. on a worksheet dedicated to
driving your chart, you may be able to simplify your VBA code
consderably.
Hope this helps.

= Marchand =