Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Name Reset
Hello,
I'm using a macro to import a text file, create two new sheets within that file with three pivot tables on each new sheet and save the file as an Excel spreadsheet. This basic macro is run for each of several text files. I used the macro record feature to create the pivot tables and these are the names Excel assigned to the tables: Sheet 1: PivotTable1, PivotTable2, PivotTable3 Sheet 2: PivotTable1, PivotTable4, PivotTable5 When I import the next text file, the names assigned a Sheet 1: PivotTable1, PivotTable6, PivotTable7 Sheet 2: PivotTable1, PivotTable8, PivotTable9 Is there any way to reset Excel's internal counter so that each time I run the macro the table names will be the same instead of incrementing by one each time? If I go back and try to import the first text file again and the macro refers to the original names for formatting the table, I get an error because the pivot table has been assigned a different number this time. Thanks! --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Name Reset
William,
I ran your macro at the end of my first text file import, ran th import again, and it blew up because it was looking for pivot table instead of 2, so it didn't appear to work. Any other suggestions -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Name Reset
Did you wait until ALL the pivot tables in the xls file had been created
before running the macro. If so, please post your code. -- XL2002 Regards William "DNewton " wrote in message ... | William, | I ran your macro at the end of my first text file import, ran the | import again, and it blew up because it was looking for pivot table 6 | instead of 2, so it didn't appear to work. | | Any other suggestions? | | | --- | Message posted from http://www.ExcelForum.com/ | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Name Reset
I ran your macro at the end of this one. Below is part of the macr
after the import..... ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData: _ "AAASSY!R6C1:R9617C13").CreatePivotTable TableDestination:="" TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3 1) ActiveSheet.Cells(3, 1).Select With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Par Number") .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("PivotTable1").AddDataFiel ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("Total Cost"), "Sum of Total Cost" xlSum Range("B5").Select Selection.Sort Key1:="R5C2", Order1:=xlDescending Type:=xlSortValues, _ OrderCustom:=1, Orientation:=xlTopToBottom Range("A1").Select Application.CommandBars("PivotTable").Visible = False Range("B3").Select ActiveSheet.PivotTables("PivotTable1").PivotSelect "" xlDataAndLabel, True Selection.Copy Range("D3").Select ActiveSheet.Paste Columns("D:D").EntireColumn.AutoFit Range("D4").Select ActiveSheet.PivotTables("PivotTable6").PivotFields ("Par Number").Orientation _ = xlHidden With ActiveSheet.PivotTables("PivotTable6").PivotFields ("Dep Held") .Orientation = xlRowField .Position = 1 End With Range("E5").Select Selection.Sort Key1:="R5C5", Order1:=xlDescending Type:=xlSortValues, _ OrderCustom:=1, Orientation:=xlTopToBottom ActiveWorkbook.ShowPivotTableFieldList = False Range("E3").Select ActiveSheet.PivotTables("PivotTable6").PivotSelect "" xlDataAndLabel, True Selection.Copy Range("G3").Select ActiveSheet.Paste Columns("G:G").EntireColumn.AutoFit Application.CutCopyMode = False ActiveWorkbook.ShowPivotTableFieldList = True Range("G4").Select ActiveSheet.PivotTables("PivotTable7").PivotFields ("Dep Held").Orientation = _ xlHidden With ActiveSheet.PivotTables("PivotTable7").PivotFields ("Ship") .Orientation = xlRowField .Position = 1 End With Range("H5").Select Selection.Sort Key1:="R5C8", Order1:=xlDescending Type:=xlSortValues, _ OrderCustom:=1, Orientation:=xlTopToBottom ActiveWorkbook.ShowPivotTableFieldList = False Range("A1").Select ActiveCell.FormulaR1C1 = "By Part Number" Range("D1").Select ActiveCell.FormulaR1C1 = "By Department" Range("G1").Select ActiveCell.FormulaR1C1 = "By Ship Serial" Range("A1:G1").Select Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("A2").Select Sheets("Sheet1").Select Sheets("Sheet1").Name = "Cost Summary" Sheets("Cost Summary").Select Sheets("Cost Summary").Move After:=Sheets(2) Sheets("AAASSY").Select ActiveSheet.Shapes.AddShape(msoShapeRectangle, 276#, 15.75, 346.5 27.75). _ Select ActiveSheet.Shapes("Rectangle 1").Select Selection.Characters.Text = _ "Data sorted by Total Hours (Column F)" & Chr(10) & "Click o Cost and Hours Summary tabs at the bottom for rollup data" With Selection.Characters(Start:=1, Length:=104).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With With Selection .Placement = xlMoveAndSize .PrintObject = False End With Selection.ShapeRange.ScaleWidth 0.91, msoFalse msoScaleFromTopLeft Selection.ShapeRange.ScaleWidth 0.97, msoFalse msoScaleFromTopLeft Selection.ShapeRange.Fill.Visible = msoTrue Selection.ShapeRange.Fill.Solid Selection.ShapeRange.Fill.ForeColor.SchemeColor = 65 Selection.ShapeRange.Fill.Transparency = 0# Selection.ShapeRange.Line.Weight = 0.75 Selection.ShapeRange.Line.DashStyle = msoLineSolid Selection.ShapeRange.Line.Style = msoLineSingle Selection.ShapeRange.Line.Transparency = 0# Selection.ShapeRange.Line.Visible = msoTrue Selection.ShapeRange.Line.ForeColor.SchemeColor = 12 Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255) Range("C10").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "AAASSY!R6C1:R617C13").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Part Number" ActiveSheet.PivotTables("PivotTable1").PivotFields ("Total Hours").Orientation _ = xlDataField ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count of Total Hours"). _ Function = xlSum Range("B5").Select Selection.Sort Key1:="R5C2", Order1:=xlDescending, Type:=xlSortValues, _ OrderCustom:=1, Orientation:=xlTopToBottom Application.CommandBars("PivotTable").Visible = False ActiveWorkbook.ShowPivotTableFieldList = False Range("B3").Select ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True Selection.Copy Range("D3").Select ActiveSheet.Paste Columns("D:D").EntireColumn.AutoFit Range("D4").Select ActiveSheet.PivotTables("PivotTable8").PivotFields ("Part Number").Orientation _ = xlHidden With ActiveSheet.PivotTables("PivotTable8").PivotFields ("Dept Held") .Orientation = xlRowField .Position = 1 End With Range("E5").Select Selection.Sort Key1:="R5C5", Order1:=xlDescending, Type:=xlSortValues, _ OrderCustom:=1, Orientation:=xlTopToBottom ActiveWorkbook.ShowPivotTableFieldList = False Range("E3").Select ActiveSheet.PivotTables("PivotTable8").PivotSelect "", xlDataAndLabel, True Selection.Copy Range("G3").Select ActiveSheet.Paste Columns("G:G").EntireColumn.AutoFit Application.CutCopyMode = False ActiveWorkbook.ShowPivotTableFieldList = True Range("G4").Select ActiveSheet.PivotTables("PivotTable9").PivotFields ("Dept Held").Orientation = _ xlHidden With ActiveSheet.PivotTables("PivotTable9").PivotFields ("Ship") .Orientation = xlRowField .Position = 1 End With Range("H5").Select Selection.Sort Key1:="R5C8", Order1:=xlDescending, Type:=xlSortValues, _ OrderCustom:=1, Orientation:=xlTopToBottom ActiveWorkbook.ShowPivotTableFieldList = False ' ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count of Total Hours"). _ ' Function = xlSum ' Range("D3").Select ' ActiveSheet.PivotTables("PivotTable8").PivotFields ("Count of Total Hours"). _ ' Function = xlSum ' Range("G3").Select ' ActiveSheet.PivotTables("PivotTable9").PivotFields ("Count of Total Hours"). _ ' Function = xlSum ' Range("B5").Select ' Selection.Sort Key1:="R5C2", Order1:=xlDescending, Type:=xlSortValues, _ ' OrderCustom:=1, Orientation:=xlTopToBottom ' Range("E5").Select ' Selection.Sort Key1:="R5C5", Order1:=xlDescending, Type:=xlSortValues, _ ' OrderCustom:=1, Orientation:=xlTopToBottom ' Range("H5").Select ' Selection.Sort Key1:="R5C8", Order1:=xlDescending, Type:=xlSortValues, _ ' OrderCustom:=1, Orientation:=xlTopToBottom Range("A1").Select ActiveCell.FormulaR1C1 = "By Part Number" Range("D1").Select ActiveCell.FormulaR1C1 = "By Department" Range("G1").Select ActiveCell.FormulaR1C1 = "By Ship Serial" Range("A1:G1").Select Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("A2").Select Sheets("Sheet2").Select Sheets("Sheet2").Name = "Hours Summary" Sheets("Hours Summary").Select Sheets("Hours Summary").Move After:=Sheets(3) Sheets(Array("Cost Summary", "Hours Summary")).Select Sheets("Cost Summary").Activate Range("A:A,D:D,G:G").Select Range("G1").Activate With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Sheets("Cost Summary").Select Range("A2").Select Sheets("Hours Summary").Select Range("A2").Select Sheets("AAASSY").Select Sheets("AAASSY").Name = "C-130 Assembly" Range("A5").Select Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "U:\My Documents\TxtFiles\LMAASRR\Aaassy.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False Application.DisplayAlerts = True ActiveWindow.Close --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table (column/row) label cache reset | Excel Discussion (Misc queries) | |||
Default Weight in Pivot Table Charts keeps getting reset | Charts and Charting in Excel | |||
Pivot Table - Formats Reset... why? | Excel Discussion (Misc queries) | |||
Reset a caption for a Pivot Table | Excel Worksheet Functions | |||
How to aviod reset of Pivot filters when reconnecting to database? | Excel Discussion (Misc queries) |