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