Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating tables using AutoFilter in Macro
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 Whe=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 Whe=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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating tables using AutoFilter in Macro
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 = On Feb 9, 9:07 am, wrote: 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 Whe=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 Whe=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating a quote looking up from tables | Excel Worksheet Functions | |||
Creating tables | Charts and Charting in Excel | |||
Creating Pivot Tables with macro Problem | Excel Programming | |||
Creating Pivot Tables inside a Macro | Excel Programming | |||
creating autofilter and using them thanks VBA | Excel Programming |