Dynamic PivotTable source - limit sources

[NikkiS]

I am still learning VBA.

I am building a workbook for users who are not very proficient with Excel.

The workbook has the potential 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 following code so that only THOSE sheets appear as an
option when a button is pressed, however they each appear 3 times. So far, I
have not been able to narrow it down to just one. Can anyone 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 'the "Archv" sheets start with #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").ChangePivotCache 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

 

[Roger]

I am still learning VBA.

I am building a workbook for users who are not very proficient with Excel.

The workbook has the potential 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 following code so that only THOSE sheets appear as an
option when a button is pressed, however they each appear 3 times. So
far, I
have not been able to narrow it down to just one. Can anyone help?

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

Next

Try just using...

myShts = ActiveWorkbook.Sheets.Count
For i = 8 To myShts 'the "Archv" sheets start with #8
myList = myList & i & " - " & ActiveWorkbook.Sheets(i).Name & " " & vbCr
Next

i.e. delete the outer loop - top and bottom 3 lines