Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Please Help! Need to add a Variable Range in my Excel Macro withPivot Tables
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Please Help! Need to add a Variable Range in my Excel Macro with P
mysheet = 2
myrow = 160

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


"fleebin" wrote:

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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Please Help! Need to add a Variable Range in my Excel Macro withP
On Mar 12, 3:18*am, Joel wrote:
mysheet = 2
myrow = 160

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



"fleebin" wrote:
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- Hide quoted text -

- Show quoted text -

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

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:=xlDatab ase,
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:=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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Please Help! Need to add a Variable Range in my Excel Macro wi
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").CreatePivotT able _
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




"fleebin" wrote:

On Mar 12, 3:18 am, Joel wrote:
mysheet = 2
myrow = 160

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



"fleebin" wrote:
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- Hide quoted text -

- Show quoted text -

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

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:=xlDatab ase,
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:=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:= _
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Range used in a macro needs to be variable DaveP Excel Worksheet Functions 3 November 4th 05 03:23 PM
Macro to copy a specified range to a variable range SWT Excel Programming 4 October 21st 05 08:24 PM
variable range in a macro AMK Excel Discussion (Misc queries) 3 July 6th 05 09:32 AM
Is it possible to set a variable range in a macro? Mary About this forum 0 June 12th 05 06:26 PM
Autosum variable range in macro Jeffrey Marcellus Excel Programming 1 September 3rd 03 10:36 PM


All times are GMT +1. The time now is 02:57 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement