Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help! Need to add a Variable Range in my Excel Macro withPivot Tables
Hi,
I would greatly appreciate your help in finally being able to add a varible range in my macro. I am creating three pivot tables in my report. I am running the report weekly. The report will have the same number of columns, but each week, a different number of rows. I am using Excel 2003, and have failed so far in accomplishing this task. The current macro works, but I occasionally see blank data in the pivot due to locked ranges. Ranges I want to be variable a "Raw1!R1C1:R160C14" "Raw2!R1C1:R132C14" "Raw3!R1C1:R153C14" I am probably doing several things wrong. Really appreciate any help ! can get! Here is the macro: Sub Audit6() ' ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Raw1!R1C1:R160C14").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Close Time", _ ColumnFields:="Level 1 Assignee", PageFields:="Severity" ActiveSheet.PivotTables("PivotTable1").PivotFields ("Close Time").Orientation = _ xlDataField ActiveWorkbook.ShowPivotTableFieldList = False Application.CommandBars("PivotTable").Visible = False Sheets("Sheet11").Select Sheets("Sheet11").Name = "Pivot1" Sheets("Raw2").Select Sheets("Raw2").Name = "Raw2" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Raw2!R1C1:R132C14").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable2", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Close Time", _ ColumnFields:="Level 1 Assignee", PageFields:="Severity" ActiveSheet.PivotTables("PivotTable2").PivotFields ("Close Time").Orientation = _ xlDataField ActiveWorkbook.ShowPivotTableFieldList = True ActiveWorkbook.ShowPivotTableFieldList = False Application.CommandBars("PivotTable").Visible = False ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Sheets("Sheet12").Select Sheets("Sheet12").Name = "Pivot2" Sheets("Raw3").Select Range("A1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Raw3!R1C1:R153C14").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable3", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="Close Time", _ ColumnFields:="Level 1 Assignee", PageFields:="Severity" ActiveSheet.PivotTables("PivotTable3").PivotFields ("Close Time").Orientation = _ xlDataField ActiveWorkbook.ShowPivotTableFieldList = True ActiveWorkbook.ShowPivotTableFieldList = False Application.CommandBars("PivotTable").Visible = False Sheets("Sheet13").Select Sheets("Sheet13").Name = "Pivot3" Range("B19").Select ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Sheets("unassigned").Select Range("A2").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "unassigned!R1C1:R34C14").CreatePivotTable TableDestination:="", TableName _ :="PivotTable4", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable4").AddFields RowFields:="Close Time", _ ColumnFields:="Level 1 Assignee", PageFields:="Severity" ActiveSheet.PivotTables("PivotTable4").PivotFields ("Close Time").Orientation = _ xlDataField ActiveWorkbook.ShowPivotTableFieldList = True ActiveWorkbook.ShowPivotTableFieldList = False Application.CommandBars("PivotTable").Visible = False ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 5 Sheets("Sheet14").Select Sheets("Sheet14").Name = "PivotU" Sheets("Pivot1").Select Charts.Add ActiveChart.SetSourceData Source:=Sheets("Pivot1").Range("A3") ActiveChart.Location Whe=xlLocationAsNewSheet Sheets("Chart1").Select ActiveWindow.SelectedSheets.Delete Range("A2").Select Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Pivot1").Range("A1:L12") ActiveChart.Location Whe=xlLocationAsNewSheet, Name:="Chart1" With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "1st Shift" .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date Closed" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "# of Incidents" End With Sheets("Pivot2").Select Range("A2").Select Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Pivot2").Range("A1:I12") ActiveChart.Location Whe=xlLocationAsNewSheet, Name:="Chart2" With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "2nd Shift" .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date Closed" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "# of Incidents" End With Sheets("Pivot3").Select Range("A2").Select Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Pivot3").Range("A1:J12") ActiveChart.Location Whe=xlLocationAsNewSheet, Name:="Chart3" With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "3rd Shift" .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date Closed" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "# of Incidents" End With Sheets("Raw").Select Range("A416").Select End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range used in a macro needs to be variable | Excel Worksheet Functions | |||
Macro to copy a specified range to a variable range | Excel Programming | |||
variable range in a macro | Excel Discussion (Misc queries) | |||
Is it possible to set a variable range in a macro? | About this forum | |||
Autosum variable range in macro | Excel Programming |