Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table help
I have the below code and getting a error (Pivot Table name not valid) when
the code is ran Sub CreatePivotTables() Dim LastRow As Long Worksheets.Add.Name = "Receiving TAT" Worksheets.Add.Name = "Screening TAT" Sheets("HP Defective Receipts in SPL ma").Select LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ TableDestination:=Range("A1"), TableName:="PivotTable1" ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Receiving TAT" With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Receiving TAT") .Orientation = xlDataField .Calculation = xlPercentOfColumn End With Charts.Add ActiveChart.SetSourceData Source:=Sheets("Receiving TAT").Range("A1") ActiveChart.Location Whe=xlLocationAsNewSheet End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table help
On Jan 21, 1:46*pm, Aaron wrote:
I have the below code and getting a error (Pivot Table name not valid) when the code is ran Sub CreatePivotTables() Dim LastRow As Long * * Worksheets.Add.Name = "Receiving TAT" * * Worksheets.Add.Name = "Screening TAT" * * Sheets("HP Defective Receipts in SPL ma").Select * * LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row * * ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ * * * * "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ * * * * TableDestination:=Range("A1"), TableName:="PivotTable1" * * ActiveSheet.PivotTables("PivotTable1").SmallGrid = False * * ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Receiving TAT" * * With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Receiving TAT") * * * * .Orientation = xlDataField * * * * .Calculation = xlPercentOfColumn * * End With * * Charts.Add * * ActiveChart.SetSourceData Source:=Sheets("Receiving TAT").Range("A1") * * ActiveChart.Location Whe=xlLocationAsNewSheet End Sub Is the pivot table name "PivotTable1" every time you run this code? or is the name changing each time? i.e. PivotTable1, PivotTable2, PivotTable3 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table help
Name changes each time. I think I also may have messed up on Table
Destination. I need it to goto tab Receiving TAT. "GTVT06" wrote: On Jan 21, 1:46 pm, Aaron wrote: I have the below code and getting a error (Pivot Table name not valid) when the code is ran Sub CreatePivotTables() Dim LastRow As Long Worksheets.Add.Name = "Receiving TAT" Worksheets.Add.Name = "Screening TAT" Sheets("HP Defective Receipts in SPL ma").Select LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ TableDestination:=Range("A1"), TableName:="PivotTable1" ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Receiving TAT" With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Receiving TAT") .Orientation = xlDataField .Calculation = xlPercentOfColumn End With Charts.Add ActiveChart.SetSourceData Source:=Sheets("Receiving TAT").Range("A1") ActiveChart.Location Whe=xlLocationAsNewSheet End Sub Is the pivot table name "PivotTable1" every time you run this code? or is the name changing each time? i.e. PivotTable1, PivotTable2, PivotTable3 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table help
On Jan 21, 2:00*pm, Aaron wrote:
Name changes each time. *I think I also may have messed up on Table Destination. I need it to goto tab Receiving TAT. "GTVT06" wrote: On Jan 21, 1:46 pm, Aaron wrote: I have the below code and getting a error (Pivot Table name not valid) when the code is ran Sub CreatePivotTables() Dim LastRow As Long * * Worksheets.Add.Name = "Receiving TAT" * * Worksheets.Add.Name = "Screening TAT" * * Sheets("HP Defective Receipts in SPL ma").Select * * LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row * * ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ * * * * "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ * * * * TableDestination:=Range("A1"), TableName:="PivotTable1" * * ActiveSheet.PivotTables("PivotTable1").SmallGrid = False * * ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Receiving TAT" * * With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Receiving TAT") * * * * .Orientation = xlDataField * * * * .Calculation = xlPercentOfColumn * * End With * * Charts.Add * * ActiveChart.SetSourceData Source:=Sheets("Receiving TAT").Range("A1") * * ActiveChart.Location Whe=xlLocationAsNewSheet End Sub Is the pivot table name "PivotTable1" every time you run this code? or is the name changing each time? i.e. PivotTable1, PivotTable2, PivotTable3- Hide quoted text - - Show quoted text - ok, the (Pivot Table name not valid) error would be because it's looking for "PivotTable1" each time, and since the name is changing each time PivotTable1 no longer exist so it's an invalid name. you may want to give the Pivot Table a unique name in the macro so each time it's ran, the name stays the same. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table help
Ok, I tried putting a unique name everywhere that pivottable1 is and I get
the same error. The debugger stops on this code. ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ TableDestination:=Sheets("Screening TAT").Range("A1"), TableName:="RTAT" "GTVT06" wrote: On Jan 21, 2:00 pm, Aaron wrote: Name changes each time. I think I also may have messed up on Table Destination. I need it to goto tab Receiving TAT. "GTVT06" wrote: On Jan 21, 1:46 pm, Aaron wrote: I have the below code and getting a error (Pivot Table name not valid) when the code is ran Sub CreatePivotTables() Dim LastRow As Long Worksheets.Add.Name = "Receiving TAT" Worksheets.Add.Name = "Screening TAT" Sheets("HP Defective Receipts in SPL ma").Select LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ TableDestination:=Range("A1"), TableName:="PivotTable1" ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Receiving TAT" With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Receiving TAT") .Orientation = xlDataField .Calculation = xlPercentOfColumn End With Charts.Add ActiveChart.SetSourceData Source:=Sheets("Receiving TAT").Range("A1") ActiveChart.Location Whe=xlLocationAsNewSheet End Sub Is the pivot table name "PivotTable1" every time you run this code? or is the name changing each time? i.e. PivotTable1, PivotTable2, PivotTable3- Hide quoted text - - Show quoted text - ok, the (Pivot Table name not valid) error would be because it's looking for "PivotTable1" each time, and since the name is changing each time PivotTable1 no longer exist so it's an invalid name. you may want to give the Pivot Table a unique name in the macro so each time it's ran, the name stays the same. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table help
On Jan 21, 2:21*pm, Aaron wrote:
Ok, I tried putting a unique name everywhere that pivottable1 is and I get the same error. *The debugger stops on this code. * * ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ * * * * "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ * * * * TableDestination:=Sheets("Screening TAT").Range("A1"), TableName:="RTAT" "GTVT06" wrote: On Jan 21, 2:00 pm, Aaron wrote: Name changes each time. *I think I also may have messed up on Table Destination. I need it to goto tab Receiving TAT. "GTVT06" wrote: On Jan 21, 1:46 pm, Aaron wrote: I have the below code and getting a error (Pivot Table name not valid) when the code is ran Sub CreatePivotTables() Dim LastRow As Long * * Worksheets.Add.Name = "Receiving TAT" * * Worksheets.Add.Name = "Screening TAT" * * Sheets("HP Defective Receipts in SPL ma").Select * * LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row * * ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ * * * * "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ * * * * TableDestination:=Range("A1"), TableName:="PivotTable1" * * ActiveSheet.PivotTables("PivotTable1").SmallGrid = False * * ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Receiving TAT" * * With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Receiving TAT") * * * * .Orientation = xlDataField * * * * .Calculation = xlPercentOfColumn * * End With * * Charts.Add * * ActiveChart.SetSourceData Source:=Sheets("Receiving TAT").Range("A1") * * ActiveChart.Location Whe=xlLocationAsNewSheet End Sub Is the pivot table name "PivotTable1" every time you run this code? or is the name changing each time? i.e. PivotTable1, PivotTable2, PivotTable3- Hide quoted text - - Show quoted text - ok, the (Pivot Table name not valid) error would be because it's looking for "PivotTable1" each time, and since the name is changing each time PivotTable1 no longer exist so it's an invalid name. you may want to give the Pivot Table a unique name in the macro so each time it's ran, the name stays the same.- Hide quoted text - - Show quoted text - Try to clear contents on the table before reusing it... Try replacing the code you just posted with this code. Sheets("Screening TAT").PivotTables("RTAT").PivotSelect "", xlDataAndLabel, True Selection.ClearContents ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ TableDestination:=Sheets("Screening TAT").Range("A1") _ , TableName:="RTAT" |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table help
Hi GTVT06
I changed the following code and it works, but it gets hung up on this code now... Code Change - added Column ("C22") ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow & "C22").CreatePivotTable _ TableDestination:=Sheets("Receiving TAT").Range("A1"), TableName:="RTAT" New Error on code: (Unable to get the Pivottable property of the Worksheet class) ActiveSheet.PivotTables("RTAT").SmallGrid = False "GTVT06" wrote: On Jan 21, 2:21 pm, Aaron wrote: Ok, I tried putting a unique name everywhere that pivottable1 is and I get the same error. The debugger stops on this code. ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ TableDestination:=Sheets("Screening TAT").Range("A1"), TableName:="RTAT" "GTVT06" wrote: On Jan 21, 2:00 pm, Aaron wrote: Name changes each time. I think I also may have messed up on Table Destination. I need it to goto tab Receiving TAT. "GTVT06" wrote: On Jan 21, 1:46 pm, Aaron wrote: I have the below code and getting a error (Pivot Table name not valid) when the code is ran Sub CreatePivotTables() Dim LastRow As Long Worksheets.Add.Name = "Receiving TAT" Worksheets.Add.Name = "Screening TAT" Sheets("HP Defective Receipts in SPL ma").Select LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ TableDestination:=Range("A1"), TableName:="PivotTable1" ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Receiving TAT" With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Receiving TAT") .Orientation = xlDataField .Calculation = xlPercentOfColumn End With Charts.Add ActiveChart.SetSourceData Source:=Sheets("Receiving TAT").Range("A1") ActiveChart.Location Whe=xlLocationAsNewSheet End Sub Is the pivot table name "PivotTable1" every time you run this code? or is the name changing each time? i.e. PivotTable1, PivotTable2, PivotTable3- Hide quoted text - - Show quoted text - ok, the (Pivot Table name not valid) error would be because it's looking for "PivotTable1" each time, and since the name is changing each time PivotTable1 no longer exist so it's an invalid name. you may want to give the Pivot Table a unique name in the macro so each time it's ran, the name stays the same.- Hide quoted text - - Show quoted text - Try to clear contents on the table before reusing it... Try replacing the code you just posted with this code. Sheets("Screening TAT").PivotTables("RTAT").PivotSelect "", xlDataAndLabel, True Selection.ClearContents ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ TableDestination:=Sheets("Screening TAT").Range("A1") _ , TableName:="RTAT" |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table help
On Jan 21, 3:05*pm, Aaron wrote:
Hi GTVT06 I changed the following code and it works, but it gets hung up on this code now... Code Change - added Column ("C22") ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ * * * * "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow & "C22").CreatePivotTable _ * * * * TableDestination:=Sheets("Receiving TAT").Range("A1"), TableName:="RTAT" New Error on code: (Unable to get the Pivottable property of the Worksheet class) ActiveSheet.PivotTables("RTAT").SmallGrid = False "GTVT06" wrote: On Jan 21, 2:21 pm, Aaron wrote: Ok, I tried putting a unique name everywhere that pivottable1 is and I get the same error. *The debugger stops on this code. * * ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ * * * * "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ * * * * TableDestination:=Sheets("Screening TAT").Range("A1"), TableName:="RTAT" "GTVT06" wrote: On Jan 21, 2:00 pm, Aaron wrote: Name changes each time. *I think I also may have messed up on Table Destination. I need it to goto tab Receiving TAT. "GTVT06" wrote: On Jan 21, 1:46 pm, Aaron wrote: I have the below code and getting a error (Pivot Table name not valid) when the code is ran Sub CreatePivotTables() Dim LastRow As Long * * Worksheets.Add.Name = "Receiving TAT" * * Worksheets.Add.Name = "Screening TAT" * * Sheets("HP Defective Receipts in SPL ma").Select * * LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row * * ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ * * * * "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ * * * * TableDestination:=Range("A1"), TableName:="PivotTable1" * * ActiveSheet.PivotTables("PivotTable1").SmallGrid = False * * ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Receiving TAT" * * With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Receiving TAT") * * * * .Orientation = xlDataField * * * * .Calculation = xlPercentOfColumn * * End With * * Charts.Add * * ActiveChart.SetSourceData Source:=Sheets("Receiving TAT").Range("A1") * * ActiveChart.Location Whe=xlLocationAsNewSheet End Sub Is the pivot table name "PivotTable1" every time you run this code? or is the name changing each time? i.e. PivotTable1, PivotTable2, PivotTable3- Hide quoted text - - Show quoted text - ok, the (Pivot Table name not valid) error would be because it's looking for "PivotTable1" each time, and since the name is changing each time PivotTable1 no longer exist so it's an invalid name. you may want to give the Pivot Table a unique name in the macro so each time it's ran, the name stays the same.- Hide quoted text - - Show quoted text - Try to clear contents on the table before reusing it... Try replacing the code you just posted with this code. * * Sheets("Screening TAT").PivotTables("RTAT").PivotSelect "", xlDataAndLabel, True * * Selection.ClearContents * * ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ * * * * "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ * * * * TableDestination:=Sheets("Screening TAT").Range("A1") _ * * * * , TableName:="RTAT"- Hide quoted text - - Show quoted text - Oh, I believe that's because if the pivot table wasn't pre-existing yet, it wont have any contents to clear. try this modified version and let me know if it works. On Error Resume Next Sheets("Sheet3").PivotTables("RTAT").PivotSelect "", xlDataAndLabel, True Selection.ClearContents ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Sheet1'!A1:G22").CreatePivotTable _ TableDestination:=Sheets("Sheet3").Range("A1") _ , TableName:="RTAT" ActiveSheet.PivotTables("RTAT").SmallGrid = False On Error GoTo 0 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table help
On Jan 21, 3:14*pm, GTVT06 wrote:
On Jan 21, 3:05*pm, Aaron wrote: Hi GTVT06 I changed the following code and it works, but it gets hung up on this code now... Code Change - added Column ("C22") ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ * * * * "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow & "C22").CreatePivotTable _ * * * * TableDestination:=Sheets("Receiving TAT").Range("A1"), TableName:="RTAT" New Error on code: (Unable to get the Pivottable property of the Worksheet class) ActiveSheet.PivotTables("RTAT").SmallGrid = False "GTVT06" wrote: On Jan 21, 2:21 pm, Aaron wrote: Ok, I tried putting a unique name everywhere that pivottable1 is and I get the same error. *The debugger stops on this code. * * ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ * * * * "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ * * * * TableDestination:=Sheets("Screening TAT").Range("A1"), TableName:="RTAT" "GTVT06" wrote: On Jan 21, 2:00 pm, Aaron wrote: Name changes each time. *I think I also may have messed up on Table Destination. I need it to goto tab Receiving TAT. "GTVT06" wrote: On Jan 21, 1:46 pm, Aaron wrote: I have the below code and getting a error (Pivot Table name not valid) when the code is ran Sub CreatePivotTables() Dim LastRow As Long * * Worksheets.Add.Name = "Receiving TAT" * * Worksheets.Add.Name = "Screening TAT" * * Sheets("HP Defective Receipts in SPL ma").Select * * LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row * * ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ * * * * "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ * * * * TableDestination:=Range("A1"), TableName:="PivotTable1" * * ActiveSheet.PivotTables("PivotTable1").SmallGrid = False * * ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Receiving TAT" * * With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Receiving TAT") * * * * .Orientation = xlDataField * * * * .Calculation = xlPercentOfColumn * * End With * * Charts.Add * * ActiveChart.SetSourceData Source:=Sheets("Receiving TAT").Range("A1") * * ActiveChart.Location Whe=xlLocationAsNewSheet End Sub Is the pivot table name "PivotTable1" every time you run this code? or is the name changing each time? i.e. PivotTable1, PivotTable2, PivotTable3- Hide quoted text - - Show quoted text - ok, the (Pivot Table name not valid) error would be because it's looking for "PivotTable1" each time, and since the name is changing each time PivotTable1 no longer exist so it's an invalid name. you may want to give the Pivot Table a unique name in the macro so each time it's ran, the name stays the same.- Hide quoted text - - Show quoted text - Try to clear contents on the table before reusing it... Try replacing the code you just posted with this code. * * Sheets("Screening TAT").PivotTables("RTAT").PivotSelect "", xlDataAndLabel, True * * Selection.ClearContents * * ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ * * * * "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow).CreatePivotTable _ * * * * TableDestination:=Sheets("Screening TAT").Range("A1") _ * * * * , TableName:="RTAT"- Hide quoted text - - Show quoted text - Oh, I believe that's because if the pivot table wasn't pre-existing yet, it wont have any contents to clear. try this modified version and let me know if it works. * * On Error Resume Next * * Sheets("Sheet3").PivotTables("RTAT").PivotSelect "", xlDataAndLabel, True * * Selection.ClearContents * * ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ * * * * "'Sheet1'!A1:G22").CreatePivotTable _ * * * * TableDestination:=Sheets("Sheet3").Range("A1") _ * * * * , TableName:="RTAT" * * * * ActiveSheet.PivotTables("RTAT").SmallGrid = False * * On Error GoTo 0- Hide quoted text - - Show quoted text - Sorry I forgot to change all the names on my code to match yours. Change Sheet3 to the appropriate sheet name and also change the source data ("'Sheet1'!A1:G22")) to fit your needs. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Copying values from pivot table to cells outside pivot table | Excel Discussion (Misc queries) | |||
Filter lines with Pivot table and non pivot table columns | Charts and Charting in Excel | |||
How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel | |||
Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming |