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

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

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

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