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
have set up a pivottable to be used with all sheets with "Archv" in the name.
I have been able to alter the Go2Sheets macro I found
(http://www.angelfire.com/biz7/julian...ans_macros.htm) so that it
will find all sheets with "Archv" in the name and present them as options in
an inputbox to be used as the new source for the pivottable. The only
problem -- each sheet name shows up multiple times! How can I narrow it down
to just once?

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