Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic PivotTable source - limit sources
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").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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic PivotTable source - limit sources
"NikkiS" wrote in message
... 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 -- Roger |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTable - multiple sources | Excel Programming | |||
Dynamic PivotTable Data Source | Excel Discussion (Misc queries) | |||
Search and replace PivotTable sources | Excel Programming | |||
PivotTable with No Source Data | Excel Programming | |||
pivottable: how to change source | Excel Programming |