Refreshing pivot tables according to scroll menu result

[Augusto_Madrid]

Hi everyone out there!

I have some 200 pivot tables and I need to do two things on them:

1) change one of the filtering variables above the tables
2) I need the new filter variable to be determined by a scroll menu that's
somewhere else in the same file, but a different sheet; e.g., if I choose
option "2" in the scroll menu, I need the tables to be filtered by variable
"2"

Can this be done at all?
Any help will be welcome, I'm stuck with macros here...
Thanks!


Augusto

 

[ryguy7272]

This code will go through the used range and do the grouping for the cells
with IndentLevels that are even. However, for IndentLevels that are odd,
there is no grouping. I tried a double-loop, like For i…Next i and For
j…Next j, but that didn’t work.


Sub Grp()
Dim lngRow As Long
Sheets("Sheet1").Select
For i = 12 To 0 Step -2
For lngRow = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Range("B" & lngRow) <> "" And Range("B" & lngRow).IndentLevel =
(i) Then
Range("B" & lngRow).Rows.Group
End If
Next lngRow
Next i
End Sub

What do I need to do to make this group both even Indents and odd Indents?

Thanks,
Ryan---

 

[Augusto_Madrid]

I'm sorry, Ryan, but I don't see how this is solving my problem...may be it's
that I'm not too much into VBA, but it looks like you posted an answer to a
Looping problem that has not much to do with my Pivot Tables modifications..

By the way, if you have any suggestions...
Thanks and best

 

[ryguy7272]

Can it be done? Excel can handle almost anything you throw at it (I'm just
starting to realize some limits now...).
Anyway, check this out:
http://www.rondebruin.nl/copy4.htm

It is a batch processing macro. Basically, the text in red must be the code
that you want to operate on each file, each sheet, etc. Copy paste the code
in black, insert your specific code where the text is red, and you are in
business!

Post back if you have additional, specific, questions.

HTH,
Ryan--

 

[ryguy7272]

Sorry Augusto!! Not sure how that got in there!!

Anyway, check out the code that is in that link and post back if you have
additional questions.

Ryan---

 

[Augusto_Madrid]

Hi again, Ryan, and thanks so much for your advice

However, I still don't see how this can help me...What this RondeBruin is
offering is help to
1) Change a cell value in one or all worksheets in every workbook in a folder
2) Change the header or footer in all worksheets in every workbook in a folder
3) Copy a range to one or all worksheets in every workbook in a folder
4) Copy a worksheet to all workbooks in a folder

But I need something different. I need to modify a pivot table according to
the choice of an option that appears on a scroll down menu; e.g., if I choose
2 in the menu, I need the pivot tables to be filtered by variable 2 (which of
course exists in the range of the table!!)

Sorry if I got it all mix up here...and thanks again

Augusto

 

[ryguy7272]

Ok, look at this (I deleted virtually all comments to trim the volume of code
down a bit.) Now, look at the this line:
'Agusto start here...
Your code goes right under that...ends when you see this:
If Err.Number > 0 Then...LEAVE THAT THERE

Finally, put your files in this folder in the appropriate directory:
C:\Agusto\


Sub Example()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String, Fnum As Long
Dim mybook As Workbook
Dim CalcMode As Long
Dim sh As Worksheet
Dim ErrorYes As Boolean

MyPath = "C:\Agusto\"

If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If
FilesInPath = Dir(MyPath & "*.xl*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
On Error GoTo 0
If Not mybook Is Nothing Then
'Agusto start here...
With mybook.Worksheets(1)
Sheets("Sheet1").Select
Range("A1").Value = "New Value"
End With

If Err.Number > 0 Then
ErrorYes = True
Err.Clear
mybook.Close savechanges:=False
Else
mybook.Close savechanges:=True
End If
On Error GoTo 0
Else
ErrorYes = True
End If

Next Fnum
End If
If ErrorYes = True Then
MsgBox "There are problems in one or more files, possible problem:" _
& vbNewLine & "protected workbook/sheet or a sheet/range that
not exist"
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub

HTH,
Ryan---