reset pivot table name

[bw]

Hi there,
I'm trying to reset the pivot table name that gets generated when you create
a pivot table without having to shut down Excel. For example, I create
PivotTable1 and PivotTable2 in a workbook and when I switch to another
workbook, the next pivot table that gets created is named PivotTable3. How do
I reset the name, as opposed to just naming it while using either the wizard
or programmatically - if I name it PivotTable1, the subsequent one generated
is still named PivotTable4. I'm using Excel 2003.
Assistance appreciated.

 

[crferguson]

Hi there,
I'm trying to reset the pivot table name that gets generated when you create
a pivot table without having to shut down Excel. For example, I create
PivotTable1 and PivotTable2 in a workbook and when I switch to another
workbook, the next pivot table that gets created is named PivotTable3. Howdo
I reset the name, as opposed to just naming it while using either the wizard
or programmatically - if I name it PivotTable1, the subsequent one generated
is still named PivotTable4. I'm using Excel 2003.
Assistance appreciated.

I don't think that's possible because of the way Excel works. When
you open a workbook you're also opening a single instance (copy) of
Excel. Then, when you open a second workbook in Excel, that second
workbook resides within the first instance of Excel so the memory is
shared between the two workbooks as far as Excel sees them (look in
Task Manager after opening the second workbook and you'll only see one
listing of Excel.) The only way around this, perhaps, would be to
open the second workbook by first opening another instance of Excel
through the Start menu and then opening the workbook from within that
new instance (File-->Open.) This can be done programmatically also by
creating new Excel objects for each workbook.

Wow, I hope you can follow that. Sounded like a riddle reading back
on it.

Hope that helps,

Cory

 

[bw]

Thanks Cory,

not the answer I was hoping for, but at least I know the direction I need to
go.

 

[kevin.hydock]

Thanks Cory,

not the answer I was hoping for, but at least I know the direction I need to
go.









- Show quoted text -

bw,

I have the exact same problem as you. I've done a lot of searching
with no answer yet. Are you using this in a macro? Is there some
reason you cannot just have excel quit and reopen?

 

[kevin.hydock]

bw,

I have the exact same problem as you. I've done a lot of searching
with no answer yet. Are you using this in a macro? Is there some
reason you cannot just haveexcelquit and reopen?- Hide quoted text -

- Show quoted text -

I ended up with some code that looks like this... hope it helps...

Sub test()

Dim i As Integer
If ActiveSheet.PivotTables.Count > 0 Then
For i = 1 To ActiveSheet.PivotTables.Count
ActiveSheet.PivotTables(i).Name = "PivotTable" & i + 1000
Next i
For i = 1 To ActiveSheet.PivotTables.Count
ActiveSheet.PivotTables(i).Name = "PivotTable" &
ActiveSheet.PivotTables.Count + 1 - i
Next i
End If

End Sub