Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTable Tools - Options/Design tabs | Excel Worksheet Functions | |||
Dynamic PivotTable source - limit sources | Excel Programming | |||
dynamic pivottable source - limit sheets | Excel Programming | |||
Dynamic PivotTable Data Source | Excel Discussion (Misc queries) | |||
copying and pasting from source sheet to destination sheet without naming source sht? | Excel Programming |