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 want the potential pivottable sources to ONLY be sheets with "Archv" in the name. I altered the Go2Sheet macro I found (http://www.angelfire.com/biz7/julian...ans_macros.htm) to try to achieve this. So far I have been able to narrow down the choices by including the "If InStr..." & telling it to start looking at choice #8. The only problem -- each sheet name shows up multiple times and if the user adds another sheet by a different name after choice #8, it shows up as well! Please 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 'sheets w/"Archv" in the name begin w/#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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic PivotTable source - limit sources | Excel Programming | |||
dynamic pivottable source - limit sheet options | 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 |