Programming using user forms

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I have a workbook with 69 worksheets.

68 of the worksheets are formatted exactly the same. Each
represents a different jurisdiction.

The 69th sheet is called "Pivots". I have code that
creates csv files by drilling down on each pivot table.
The code is something like...

Dim pt as PivotTable
For each pt in Worksheets("Pivots").Pivottables
Do some stuff
Next pt

This works very well but I may always want to do
the "stuff" on every pivot table. Is there a way, with
user forms or something, to select certain pivot tables
(based on their name, maybe?). Maybe using check boxes?
 
You could list the pivotTables in a range named PTNames, and mark some
with an "X" in the column to the left. Then, work with the marked
tables, e.g.:

Sub PivotMarked()
Dim c As Range
Dim pt As PivotTable
Dim ws As Worksheet
Set ws = Worksheets("Pivots")

For Each c In Range("PTNames")
If UCase(c.Offset(0, -1).Value) = UCase("X") Then
MsgBox ws.PivotTables(c.Value).Name
End If
Next c
End Sub
 
If you know the names:

Option Explicit
Sub testme02()

Dim myPTNames As Variant
Dim iCtr As Long
Dim PT As PivotTable

myPTNames = Array("pt1", "Pt2", "pt8", "pt10")

For iCtr = LBound(myPTNames) To UBound(myPTNames)
Set PT = Nothing
On Error Resume Next
Set PT = Worksheets("pivots").PivotTables(myPTNames(iCtr))
If PT Is Nothing Then
MsgBox myPTNames(iCtr) & " doesn't exist: "
'Exit Sub
Else
'do some neat stuff
End If
Next iCtr

End Sub
 
Back
Top