Please Help! Need to add a Variable Range in my Excel Macro withPivot Tables

F

fleebin

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 are:
"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:=xlDatabase,
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:=xlDatabase,
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:=xlDatabase,
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:=xlDatabase,
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 Where:=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 Where:=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 Where:=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 Where:=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
 
F

fleebin

mysheet = 2
myrow = 160

"Raw" & mysheet & "!R1C1:R" & myrow & "C14"










- Show quoted text -
Click to expand...

=======================================

I am receiving the following error *
(no matter where I put)
mysheet = 2
myrow = 160

* Compile Error:

Expected: List Seperator or )

I am seeing the cursor at the . (I.E.)
.CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

After
"Raw" & mysheet & "!R1C1:R" & myrow & "C14"



Sorry, I am just learning about VBA!
I only modified the code for the first pivot as a test.

This is the code currently:

ActiveWindow.SelectedSheets.Delete
mysheet = 2
myrow = 160
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
"Raw" & mysheet & "!R1C1:R" & myrow & "C14".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:=xlDatabase,
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:=xlDatabase,
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:=xlDatabase,
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 Where:=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 Where:=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 Where:=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 Where:=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
 
J

Joel

You lost a period when you where editing your original code. When you post
code on this website the site automatically adds a Line feed after 80
characters which can cause errors. I reduced the length of the lines by
adding a line continuation character "_" and the end of some lines. See code
below. I believe there should be a space between close and time "Close Time"
in the code below. Not 100% sure because of the way the lines wrap in the
posted code.


Sub Audit6()

ActiveWindow.SelectedSheets.Delete
mysheet = 2
myrow = 160
ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:="Raw" & mysheet & "!R1C1:R" & myrow &
"C14").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:=xlDatabase, _
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:=xlDatabase, _
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:=xlDatabase, _
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 Where:=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 Where:=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 Where:=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 Where:=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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to change the row number in macro code? 1
Code to Create Chart 0
Date values not appearing correclty in the x-axis 1
add a chart in a Add-In 1
Charts in Multiple Sheet in Excel 1
reverse axis not working as before... 0
Graph - Runtime Error 1004 5
"Lines on 2 Axes" fails on Excel 2007 1

Top