|
urgent-pivot table
Hi..
I am completely zapped at whats happening...i was running running the macro very nicely and all of a sudden the its giving error and i am unable to resolve it. I was creating a pivot table in a new sheet. and now the new sheet is not getting created somehow (#$%^&) and its processing on the same sheet from whihc it has to take pivot values and gives a error "do u want to replace of destination cells in RawData?" here is the piece of code where i create a pivot table in a new sheet. the new sheet gets created but somehow the pivot is not being created ...! Set NewSheet = Worksheets.Add (Befo=ActiveWorkbook.Worksheets.Item(1)) NewSheet.Name = "test" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ rawDataWorksheetName & "!R1C1:R" & LastCellNum & "C" & CU).CreatePivotTable TableDestination:="", TableName:= _ "PivotTable5", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveWorkbook.ShowPivotTableFieldList = True With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Test_Loc") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Package") .Orientation = xlRowField .Position = 2 End With With ActiveSheet.PivotTables("PivotTable5").PivotFields ("PL") .Orientation = xlRowField .Position = 3 End With ActiveSheet.PivotTables("PivotTable5").AddDataFiel d ActiveSheet.PivotTables( _ "PivotTable5").PivotFields(trapCumVal), "Sum of " & trapCumVal, xlSum ActiveWorkbook.ShowPivotTableFieldList = False Range("B6").Select ActiveSheet.PivotTables("PivotTable5").PivotFields ("Package").Subtotals = Array _ (False, False, False, False, False, False, False, False, False, False, False, False) Range("A81").Select ActiveSheet.PivotTables("PivotTable5").PivotFields ("Test_Loc").Subtotals = _ Array(False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.Select sheetname = "MSP" please do suggest thanks |
urgent-pivot table
It looks like you create the sheet, but don't use that sheet as the
TableDestination. Try changing the line to include it: '============================ Dim wb As Workbook Set wb = ActiveWorkbook Set NewSheet = Worksheets.Add _ (Befo=ActiveWorkbook.Worksheets.Item(1)) NewSheet.Name = "test" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _ SourceData:=rawDataWorksheetName & "!R1C1:R" & _ LastCellNum & "C" & CU).CreatePivotTable _ TableDestination:="'[" & wb.Name & "]test'!R3C1", _ TableName:="PivotTable5" '========================= monika wrote: Hi.. I am completely zapped at whats happening...i was running running the macro very nicely and all of a sudden the its giving error and i am unable to resolve it. I was creating a pivot table in a new sheet. and now the new sheet is not getting created somehow (#$%^&) and its processing on the same sheet from whihc it has to take pivot values and gives a error "do u want to replace of destination cells in RawData?" here is the piece of code where i create a pivot table in a new sheet. the new sheet gets created but somehow the pivot is not being created ...! Set NewSheet = Worksheets.Add (Befo=ActiveWorkbook.Worksheets.Item(1)) NewSheet.Name = "test" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ rawDataWorksheetName & "!R1C1:R" & LastCellNum & "C" & CU).CreatePivotTable TableDestination:="", TableName:= _ "PivotTable5", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveWorkbook.ShowPivotTableFieldList = True With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Test_Loc") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Package") .Orientation = xlRowField .Position = 2 End With With ActiveSheet.PivotTables("PivotTable5").PivotFields ("PL") .Orientation = xlRowField .Position = 3 End With ActiveSheet.PivotTables("PivotTable5").AddDataFiel d ActiveSheet.PivotTables( _ "PivotTable5").PivotFields(trapCumVal), "Sum of " & trapCumVal, xlSum ActiveWorkbook.ShowPivotTableFieldList = False Range("B6").Select ActiveSheet.PivotTables("PivotTable5").PivotFields ("Package").Subtotals = Array _ (False, False, False, False, False, False, False, False, False, False, False, False) Range("A81").Select ActiveSheet.PivotTables("PivotTable5").PivotFields ("Test_Loc").Subtotals = _ Array(False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.Select sheetname = "MSP" please do suggest thanks -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
urgent-pivot table
hi debra
thanks for taking interest in replying to my query i did what u said...see below. But still the pivot table is not getting created. i get the new sheet created but the pivot table is not getting created in that new sheet test. pls do suggest... thanks again Set NewSheet = Worksheets.Add (Befo=ActiveWorkbook.Worksheets.Item(1)) NewSheet.Name = "test" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ rawDataWorksheetName & "!R1C1:R" & LastCellNum & "C" & CU).CreatePivotTable TableDestination:="'[Book1.xls]test'!R3C1", TableName:= _ "PivotTable5", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveWorkbook.ShowPivotTableFieldList = True -----Original Message----- It looks like you create the sheet, but don't use that sheet as the TableDestination. Try changing the line to include it: '============================ Dim wb As Workbook Set wb = ActiveWorkbook Set NewSheet = Worksheets.Add _ (Befo=ActiveWorkbook.Worksheets.Item(1)) NewSheet.Name = "test" ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base, _ SourceData:=rawDataWorksheetName & "!R1C1:R" & _ LastCellNum & "C" & CU).CreatePivotTable _ TableDestination:="'[" & wb.Name & "]test'!R3C1", _ TableName:="PivotTable5" '========================= monika wrote: Hi.. I am completely zapped at whats happening...i was running running the macro very nicely and all of a sudden the its giving error and i am unable to resolve it. I was creating a pivot table in a new sheet. and now the new sheet is not getting created somehow (#$%^&) and its processing on the same sheet from whihc it has to take pivot values and gives a error "do u want to replace of destination cells in RawData?" here is the piece of code where i create a pivot table in a new sheet. the new sheet gets created but somehow the pivot is not being created ...! Set NewSheet = Worksheets.Add (Befo=ActiveWorkbook.Worksheets.Item(1)) NewSheet.Name = "test" ActiveWorkbook.PivotCaches.Add (SourceType:=xlDatabase, SourceData:= _ rawDataWorksheetName & "!R1C1:R" & LastCellNum & "C" & CU).CreatePivotTable TableDestination:="", TableName:= _ "PivotTable5", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveWorkbook.ShowPivotTableFieldList = True With ActiveSheet.PivotTables ("PivotTable5").PivotFields ("Test_Loc") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables ("PivotTable5").PivotFields ("Package") .Orientation = xlRowField .Position = 2 End With With ActiveSheet.PivotTables ("PivotTable5").PivotFields ("PL") .Orientation = xlRowField .Position = 3 End With ActiveSheet.PivotTables("PivotTable5").AddDataFiel d ActiveSheet.PivotTables( _ "PivotTable5").PivotFields(trapCumVal), "Sum of " & trapCumVal, xlSum ActiveWorkbook.ShowPivotTableFieldList = False Range("B6").Select ActiveSheet.PivotTables("PivotTable5").PivotFields ("Package").Subtotals = Array _ (False, False, False, False, False, False, False, False, False, False, False, False) Range("A81").Select ActiveSheet.PivotTables("PivotTable5").PivotFields ("Test_Loc").Subtotals = _ Array(False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.Select sheetname = "MSP" please do suggest thanks -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html . |
urgent-pivot table
i resolved this error..
it was a very silly mistake. the last column was coming one extra which caused the error. -----Original Message----- hi debra thanks for taking interest in replying to my query i did what u said...see below. But still the pivot table is not getting created. i get the new sheet created but the pivot table is not getting created in that new sheet test. pls do suggest... thanks again Set NewSheet = Worksheets.Add (Befo=ActiveWorkbook.Worksheets.Item(1)) NewSheet.Name = "test" ActiveWorkbook.PivotCaches.Add (SourceType:=xlDatabase, SourceData:= _ rawDataWorksheetName & "!R1C1:R" & LastCellNum & "C" & CU).CreatePivotTable TableDestination:="'[Book1.xls]test'!R3C1", TableName:= _ "PivotTable5", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveWorkbook.ShowPivotTableFieldList = True -----Original Message----- It looks like you create the sheet, but don't use that sheet as the TableDestination. Try changing the line to include it: '============================ Dim wb As Workbook Set wb = ActiveWorkbook Set NewSheet = Worksheets.Add _ (Befo=ActiveWorkbook.Worksheets.Item(1)) NewSheet.Name = "test" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDat abase, _ SourceData:=rawDataWorksheetName & "!R1C1:R" & _ LastCellNum & "C" & CU).CreatePivotTable _ TableDestination:="'[" & wb.Name & "]test'!R3C1", _ TableName:="PivotTable5" '========================= monika wrote: Hi.. I am completely zapped at whats happening...i was running running the macro very nicely and all of a sudden the its giving error and i am unable to resolve it. I was creating a pivot table in a new sheet. and now the new sheet is not getting created somehow (#$%^&) and its processing on the same sheet from whihc it has to take pivot values and gives a error "do u want to replace of destination cells in RawData?" here is the piece of code where i create a pivot table in a new sheet. the new sheet gets created but somehow the pivot is not being created ...! Set NewSheet = Worksheets.Add (Befo=ActiveWorkbook.Worksheets.Item(1)) NewSheet.Name = "test" ActiveWorkbook.PivotCaches.Add (SourceType:=xlDatabase, SourceData:= _ rawDataWorksheetName & "!R1C1:R" & LastCellNum & "C" & CU).CreatePivotTable TableDestination:="", TableName:= _ "PivotTable5", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveWorkbook.ShowPivotTableFieldList = True With ActiveSheet.PivotTables ("PivotTable5").PivotFields ("Test_Loc") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables ("PivotTable5").PivotFields ("Package") .Orientation = xlRowField .Position = 2 End With With ActiveSheet.PivotTables ("PivotTable5").PivotFields ("PL") .Orientation = xlRowField .Position = 3 End With ActiveSheet.PivotTables ("PivotTable5").AddDataField ActiveSheet.PivotTables( _ "PivotTable5").PivotFields(trapCumVal), "Sum of " & trapCumVal, xlSum ActiveWorkbook.ShowPivotTableFieldList = False Range("B6").Select ActiveSheet.PivotTables("PivotTable5").PivotFields ("Package").Subtotals = Array _ (False, False, False, False, False, False, False, False, False, False, False, False) Range("A81").Select ActiveSheet.PivotTables("PivotTable5").PivotFields ("Test_Loc").Subtotals = _ Array(False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.Select sheetname = "MSP" please do suggest thanks -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html . . |
urgent-pivot table
One way to avoid this kind of stuff:
TableDestination:="'[" & wb.Name & "]test'!R3C1", _ is to use: TableDestination:=newsheet.cells(3,1).address(exte rnal:=true), _ or TableDestination:=newsheet.range("A3").address(ext ernal:=true), _ But I wouldn't insert a newsheet first anyway--just let the pivottable do it. And since the sheet with the new pivottable is active, I could use: set newSheet = activesheet after the lines that created the new worksheet. Debra Dalgleish wrote: It looks like you create the sheet, but don't use that sheet as the TableDestination. Try changing the line to include it: '============================ Dim wb As Workbook Set wb = ActiveWorkbook Set NewSheet = Worksheets.Add _ (Befo=ActiveWorkbook.Worksheets.Item(1)) NewSheet.Name = "test" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _ SourceData:=rawDataWorksheetName & "!R1C1:R" & _ LastCellNum & "C" & CU).CreatePivotTable _ TableDestination:="'[" & wb.Name & "]test'!R3C1", _ TableName:="PivotTable5" '========================= monika wrote: Hi.. I am completely zapped at whats happening...i was running running the macro very nicely and all of a sudden the its giving error and i am unable to resolve it. I was creating a pivot table in a new sheet. and now the new sheet is not getting created somehow (#$%^&) and its processing on the same sheet from whihc it has to take pivot values and gives a error "do u want to replace of destination cells in RawData?" here is the piece of code where i create a pivot table in a new sheet. the new sheet gets created but somehow the pivot is not being created ...! Set NewSheet = Worksheets.Add (Befo=ActiveWorkbook.Worksheets.Item(1)) NewSheet.Name = "test" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ rawDataWorksheetName & "!R1C1:R" & LastCellNum & "C" & CU).CreatePivotTable TableDestination:="", TableName:= _ "PivotTable5", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveWorkbook.ShowPivotTableFieldList = True With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Test_Loc") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Package") .Orientation = xlRowField .Position = 2 End With With ActiveSheet.PivotTables("PivotTable5").PivotFields ("PL") .Orientation = xlRowField .Position = 3 End With ActiveSheet.PivotTables("PivotTable5").AddDataFiel d ActiveSheet.PivotTables( _ "PivotTable5").PivotFields(trapCumVal), "Sum of " & trapCumVal, xlSum ActiveWorkbook.ShowPivotTableFieldList = False Range("B6").Select ActiveSheet.PivotTables("PivotTable5").PivotFields ("Package").Subtotals = Array _ (False, False, False, False, False, False, False, False, False, False, False, False) Range("A81").Select ActiveSheet.PivotTables("PivotTable5").PivotFields ("Test_Loc").Subtotals = _ Array(False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.Select sheetname = "MSP" please do suggest thanks -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Dave Peterson |
urgent-pivot table
You're right -- I don't know what I was thinking. In my own code, I'd
just let Excel create the sheet, then I'd give it a name. (My only excuse is that it was really late <g) Dave Peterson wrote: One way to avoid this kind of stuff: TableDestination:="'[" & wb.Name & "]test'!R3C1", _ is to use: TableDestination:=newsheet.cells(3,1).address(exte rnal:=true), _ or TableDestination:=newsheet.range("A3").address(ext ernal:=true), _ But I wouldn't insert a newsheet first anyway--just let the pivottable do it. And since the sheet with the new pivottable is active, I could use: set newSheet = activesheet after the lines that created the new worksheet. Debra Dalgleish wrote: It looks like you create the sheet, but don't use that sheet as the TableDestination. Try changing the line to include it: '============================ Dim wb As Workbook Set wb = ActiveWorkbook Set NewSheet = Worksheets.Add _ (Befo=ActiveWorkbook.Worksheets.Item(1)) NewSheet.Name = "test" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDat abase, _ SourceData:=rawDataWorksheetName & "!R1C1:R" & _ LastCellNum & "C" & CU).CreatePivotTable _ TableDestination:="'[" & wb.Name & "]test'!R3C1", _ TableName:="PivotTable5" '========================= monika wrote: Hi.. I am completely zapped at whats happening...i was running running the macro very nicely and all of a sudden the its giving error and i am unable to resolve it. I was creating a pivot table in a new sheet. and now the new sheet is not getting created somehow (#$%^&) and its processing on the same sheet from whihc it has to take pivot values and gives a error "do u want to replace of destination cells in RawData?" here is the piece of code where i create a pivot table in a new sheet. the new sheet gets created but somehow the pivot is not being created ...! Set NewSheet = Worksheets.Add (Befo=ActiveWorkbook.Worksheets.Item(1)) NewSheet.Name = "test" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ rawDataWorksheetName & "!R1C1:R" & LastCellNum & "C" & CU).CreatePivotTable TableDestination:="", TableName:= _ "PivotTable5", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveWorkbook.ShowPivotTableFieldList = True With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Test_Loc") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Package") .Orientation = xlRowField .Position = 2 End With With ActiveSheet.PivotTables("PivotTable5").PivotFields ("PL") .Orientation = xlRowField .Position = 3 End With ActiveSheet.PivotTables("PivotTable5").AddDataFiel d ActiveSheet.PivotTables( _ "PivotTable5").PivotFields(trapCumVal), "Sum of " & trapCumVal, xlSum ActiveWorkbook.ShowPivotTableFieldList = False Range("B6").Select ActiveSheet.PivotTables("PivotTable5").PivotFields ("Package").Subtotals = Array _ (False, False, False, False, False, False, False, False, False, False, False, False) Range("A81").Select ActiveSheet.PivotTables("PivotTable5").PivotFields ("Test_Loc").Subtotals = _ Array(False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.Select sheetname = "MSP" please do suggest thanks -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
urgent-pivot table
I like to use:
It was too {early|close to lunch|close to quitting|late}. Always leave your options open <vbg. Debra Dalgleish wrote: You're right -- I don't know what I was thinking. In my own code, I'd just let Excel create the sheet, then I'd give it a name. (My only excuse is that it was really late <g) Dave Peterson wrote: One way to avoid this kind of stuff: TableDestination:="'[" & wb.Name & "]test'!R3C1", _ is to use: TableDestination:=newsheet.cells(3,1).address(exte rnal:=true), _ or TableDestination:=newsheet.range("A3").address(ext ernal:=true), _ But I wouldn't insert a newsheet first anyway--just let the pivottable do it. And since the sheet with the new pivottable is active, I could use: set newSheet = activesheet after the lines that created the new worksheet. Debra Dalgleish wrote: It looks like you create the sheet, but don't use that sheet as the TableDestination. Try changing the line to include it: '============================ Dim wb As Workbook Set wb = ActiveWorkbook Set NewSheet = Worksheets.Add _ (Befo=ActiveWorkbook.Worksheets.Item(1)) NewSheet.Name = "test" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDat abase, _ SourceData:=rawDataWorksheetName & "!R1C1:R" & _ LastCellNum & "C" & CU).CreatePivotTable _ TableDestination:="'[" & wb.Name & "]test'!R3C1", _ TableName:="PivotTable5" '========================= monika wrote: Hi.. I am completely zapped at whats happening...i was running running the macro very nicely and all of a sudden the its giving error and i am unable to resolve it. I was creating a pivot table in a new sheet. and now the new sheet is not getting created somehow (#$%^&) and its processing on the same sheet from whihc it has to take pivot values and gives a error "do u want to replace of destination cells in RawData?" here is the piece of code where i create a pivot table in a new sheet. the new sheet gets created but somehow the pivot is not being created ...! Set NewSheet = Worksheets.Add (Befo=ActiveWorkbook.Worksheets.Item(1)) NewSheet.Name = "test" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ rawDataWorksheetName & "!R1C1:R" & LastCellNum & "C" & CU).CreatePivotTable TableDestination:="", TableName:= _ "PivotTable5", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveWorkbook.ShowPivotTableFieldList = True With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Test_Loc") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Package") .Orientation = xlRowField .Position = 2 End With With ActiveSheet.PivotTables("PivotTable5").PivotFields ("PL") .Orientation = xlRowField .Position = 3 End With ActiveSheet.PivotTables("PivotTable5").AddDataFiel d ActiveSheet.PivotTables( _ "PivotTable5").PivotFields(trapCumVal), "Sum of " & trapCumVal, xlSum ActiveWorkbook.ShowPivotTableFieldList = False Range("B6").Select ActiveSheet.PivotTables("PivotTable5").PivotFields ("Package").Subtotals = Array _ (False, False, False, False, False, False, False, False, False, False, False, False) Range("A81").Select ActiveSheet.PivotTables("PivotTable5").PivotFields ("Test_Loc").Subtotals = _ Array(False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.Select sheetname = "MSP" please do suggest thanks -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Dave Peterson |
| All times are GMT +1. The time now is 09:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com