pivot tables macro

[Hru48]

Hey,

I’m having a lot of trouble with pivot tables. I am trying to get
macro to create them automatically by taking data from one sheet an
making a pivot table in another (‘SheetRef’).

I can get it to work if I create a blank sheet for each new pivot b
changing the table destination to “”. But when I try to specify that
want them to be created on a specific sheet I get an error.

I have been trying to put the sheet name in the table desitnation:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'01'!R8C1:R477C2").CreatePivotTable TableDestination:= HERE
_
TableName:="PivotTable1"

Am I doing something wrong? If someone can point me in the righ
direction I would really appreciate it. I have put the whole recordin
of createing th pivot underneath if that helps?

Cheers

Hayley



Range("A8:B8").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:
_
"'01'!R8C1:R477C2").CreatePivotTabl
TableDestination:=Range("G1"), _
TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Agen
Number/Name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Campaig
Type")
.Orientation = xlDataField
.Position = 1
End With
Application.CommandBars("PivotTable").Visible = False
End Su

 

[Bernie Deitrick]

For Example:

TableDestination:=Worksheets("'SheetRef'").Range("K4")

HTH,
Bernie
MS Excel MVP

 

[Hru48]

Thanks, but I get the error ' Subscript out of range'. any ideas?

 

[Dave Peterson]

untested:

TableDestination:=Worksheets("SheetRef").Range("K4")

 

[Bernie Deitrick]

Sorry, I accidentally copied the single quotes along with the sheet name - it should be something
like

TableDestination:=Worksheets("SheetRef").Range("G1")

HTH,
Bernie
MS Excel MVP

 

[Hru48]

Ok it worked that time thanks alot!

 

[Hru48]

Ok it worked that time thanks alot!

 

[Hru48]

Right so this macro works for the first pivot:

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 7/6/2006 by hru48
'

Range("A8:B8").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=
_
"'01'!R8C1:R477C2").CreatePivotTable
TableDestination:=Worksheets("SheetRef").Range("k1 "), _
TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Agent
Number/Name")
..Orientation = xlRowField
..Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Campaign
Type")
..Orientation = xlDataField
..Position = 1
End With
Windows("New_jersey_agent.xls").Activate
Range("B11").Select
End Sub


Does anyone know how I would approach getting it to work for another
one with the same properties?

I'm aiming to get this macro to repeat for all number worksheets (above
was 01) until it gets to 'SheetRef' where it should stop. The pivot
should all be place on sheetRef as well one column apart from each
other. Is this possible?

I'm having trouble as I can't figure out how to change the source data
or the table name - any ideas?

Cheers

 

[Bernie Deitrick]

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub Macro4FixedToLoop()
Dim mySht As Worksheet
Dim myR As Range
Dim myC As Range
Dim i As Integer

Set myC = Worksheets("SheetRef").Range("K1")
i = 1

For Each mySht In Worksheets
If mySht.Name <> "SheetRef" Then
Set myR = mySht.Range("A8:B8")
Set myR = mySht.Range(myR, myR.End(xlDown))
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=myR).CreatePivotTable TableDestination:=myC, _
TableName:="PivotTable" & i
Worksheets("SheetRef").PivotTables("PivotTable" & i).SmallGrid = False
With Worksheets("SheetRef").PivotTables("PivotTable" & i) _
.PivotFields("Agent Number/Name")
.Orientation = xlRowField
.Position = 1
End With
With Worksheets("SheetRef").PivotTables("PivotTable" & i) _
.PivotFields("Campaign Type")
.Orientation = xlDataField
.Position = 1
End With
i = i + 1
Set myC = myC.Offset(0, 3)
End If
Next mySht
End Sub

 

[Hru48]

looks great but I get a syntax error for this bit:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=myR).CreatePivotTable TableDestination:=myC, _
TableName:="PivotTable" & i

 

[Bernie Deitrick]

Perhaps the extra space in xlDatab ase? - should be xlDatabase. The code worked fine when I tested
it.

HTH,
Bernie
MS Excel MVP

 

[Hru48]

hmm I changed it from

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,

to

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,


but now I get an invalid proceedure, call or argument error

 

[Bernie Deitrick]

Your newsreader or web site interface may have introduced errors. Post your email (with the @
replace by AT) and I will send you a working version.

HTH,
Bernie
MS Excel MVP

 

[Hru48]

Thanks,

Its

hru48ATallstate.co

 

[Bernie Deitrick]

Sent....