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:=xlDatab ase,
SourceData:=rng)
Set pt = pc.CreatePivotTable(sht, val2)

.....it breaks at the last line

Thanks