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/

You could try adding this to your current macro after you have created all
the pivot tables.

Sub test()
Dim i As Integer
Dim ws As Worksheet
For Each ws In Worksheets
If ws.PivotTables.Count 0 Then
For i = 1 To ws.PivotTables.Count
ws.PivotTables(i).Name = "PivotTable" & i + 1000
Next i
For i = 1 To ws.PivotTables.Count
ws.PivotTables(i).Name = "PivotTable" & i
Next i
End If
Next ws
End Sub



--
XL2002
Regards

William



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

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

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/
|

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/