Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic pivottable source - limit sheets
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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic PivotTable source - limit sources | Excel Programming | |||
Dynamic PivotTable Data Source | Excel Discussion (Misc queries) | |||
I NEED TO KNOW THE SOURCE OF A PIVOTTABLE FROM ACCESS | Excel Discussion (Misc queries) | |||
pivottable: how to change source | Excel Programming | |||
PivotTable Source & Destination | Excel Programming |