Issue building pivot tables with certain names

[KB]

Hi

I have reports that build pivot tables based on a variable lifted from the
name of the tab/sheet so that I can ensure the right regional data appears on
the correct sheet. This code has been running problem free for over 6 months
but we recently changed the names of the regions and I now have a problem -
the new regional names are based on a series e.g. C01B and this seems to make
the code fail. If I prefix the name to SC01B the pivot table is built but
starting on the wrong sheet.

Has anyone experienced something similar? Can you tell me what is going on?

The code I am using is:

Dim pc As PivotCache
Dim pt As PivotTable
Dim pi As PivotItem
Dim val2 As String * 11, sht As String * 9
Dim dte1 As Date

val2 = val1 & "_Pivot1" 'name of the
pivot table
sht = val1 & "!R7C5" 'destination
of the pivot table

Sheets(val1).Select

' inserts a pivot table
Set pc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=rng)
Set pt = pc.CreatePivotTable(sht, val2)

.....it breaks at the last line

Thanks

 

[Bernie Deitrick]

--
HTH,
Bernie
MS Excel MVP
KB,

It sounds like the RC naming limitation - if Excel can interpret a name as an RC address, it is
disallowed - while the B at the end should keep that from happening, the C<number><number> may be
throwing Excel. So, as you found, don't start named items with an R, C, or RC.... If you try to
create the names A, B, and C, Excel will take A and B, but will create C_ instead of C....

HTH,
Bernie