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