Hey,

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

I can get it to work if I create a blank sheet for each new pivot by
changing the table destination to “”. But when I try to specify that I
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:=xlDatab ase, SourceData:= _
"'01'!R8C1:R477C2").CreatePivotTable TableDestination:= HERE,
_
TableName:="PivotTable1"

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

Cheers

Hayley



Range("A8:B8").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"'01'!R8C1:R477C2").CreatePivotTable
TableDestination:=Range("G1"), _
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
Application.CommandBars("PivotTable").Visible = False
End Sub


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=558821

For Example:

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

HTH,
Bernie
MS Excel MVP


"Hru48" wrote in message
...

Hey,

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

I can get it to work if I create a blank sheet for each new pivot by
changing the table destination to "". But when I try to specify that I
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:=xlDatab ase, SourceData:= _
"'01'!R8C1:R477C2").CreatePivotTable TableDestination:= HERE,
_
TableName:="PivotTable1"

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

Cheers

Hayley



Range("A8:B8").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"'01'!R8C1:R477C2").CreatePivotTable
TableDestination:=Range("G1"), _
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
Application.CommandBars("PivotTable").Visible = False
End Sub


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=558821

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


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=558821

untested:

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



Hru48 wrote:

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

--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=558821

--

Dave Peterson

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" wrote in message
...

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


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=558821

Ok it worked that time thanks alot!


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=558821

Ok it worked that time thanks alot!


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=558821

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:=xlDatab ase, 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


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=558821

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:=xlDatab ase, _
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" wrote in message
...

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:=xlDatab ase, 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


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=558821

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


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=558821

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

HTH,
Bernie
MS Excel MVP


"Hru48" wrote in message
...

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


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=558821

hmm I changed it from

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

to

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


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


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=558821

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" wrote in message
...

hmm I changed it from

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

to

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


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


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=558821

Thanks,

Its

hru48ATallstate.com


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=558821

Sent....

--
HTH,
Bernie
MS Excel MVP


"Hru48" wrote in message
...

Thanks,

Its

hru48ATallstate.com


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=558821