dynamic pivottable source - limit sheet options
 

I am still learning VBA. A lot of my code comes from recording and/or
finding examples on the web & trying to change it for my needs.

I am setting up a workbook for users who are Excel newbies (will be using
Excel 2003). There is potential for this workbook to have many sheets. I
want the potential pivottable sources to ONLY be sheets with "Archv" in the
name. I altered the Go2Sheet macro I found
(http://www.angelfire.com/biz7/julian...ans_macros.htm) to try to
achieve this. So far I have been able to narrow down the choices by
including the "If InStr..." & telling it to start looking at choice #8. The
only problem -- each sheet name shows up multiple times and if the user adds
another sheet by a different name after choice #8, it shows up as well!
Please help!!


Sub ChangeSource()
Dim myRng As Range
Dim MyPvt As PivotTable
Set MyPvt = ActiveWorkbook.Worksheets("All Archv'd
Pivt").PivotTables("AllArchv'dPivt")

For Each ws In Sheets
If InStr(ws.Name, "Archv") 1 Then
With ws
myShts = ActiveWorkbook.Sheets.Count
For i = 8 To myShts 'sheets w/"Archv" in the name begin w/#8
myList = myList & i & " - " & ActiveWorkbook.Sheets(i).Name
& " " & vbCr
Next i
End With
End If

Next
Dim mySht As Single
On Error GoTo cancel
Application.EnableEvents = False
mySht = InputBox("Choose the # of the Archived Data sheet you
want to use:" & vbCr & vbCr & myList)
Application.EnableEvents = True

Set myRng = Sheets(mySht).Range("a1:u3500")

With Worksheets(mySht)
'Update the Source data of the PT
Worksheets("All Archv'd
Pivt").PivotTables("AllArchv'dPivt").ChangePivotCa che ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase,
SourceData:=myRng.Address(external:=True)) _
'Refresh the data in the PT
MyPvt.RefreshTable
End With

Range("c11").Select
ActiveCell.Formula = Sheets(mySht).Name

'Clear Object Variables
Set myRng = Nothing
Set MyPvt = Nothing
GoTo ChangeSource_end

cancel:
MsgBox ("Process Cancelled by You")

ChangeSource_end:
End Sub