phil152003
Board Regular
- Joined
- Mar 11, 2011
- Messages
- 89
Hi, Below is some code I've been working with. Basically, my macro does the following:
I thought I'd done this with the code below, but for some reason it is giving me an error 400 upoon trying to perform the simple task of selecting cell A1! If I remove the bolded code though, the macro works perfectly, the file just doens't look as good.
Code:
If you could shed any light on why this error is occuring that would be great!
- Selects the first option in the page filter of a pivot table
- If the results of the pivot table aren't empy then:
- Change the filter of a second pivot table (on a different sheet) to match the first filter
- Copy these two sheets containing the pivot tables and paste value them into separate sheets (the version that we send out only shows the paste value version so they can't touch the formulas etc)
- Save as a file name containing the value in the pivot table filter.
I thought I'd done this with the code below, but for some reason it is giving me an error 400 upoon trying to perform the simple task of selecting cell A1! If I remove the bolded code though, the macro works perfectly, the file just doens't look as good.
Code:
Code:
Sub CycleFilter()
Dim p As PivotItem
With Sheets("Client Detail LIVE").PivotTables("PivotTable1").PageFields("CM Name")
For Each p In .PivotItems
.CurrentPage = "" & p & ""
If WorksheetFunction.CountA(Sheets("Client Detail LIVE").Range("A7:A65536")) <> 0 Then
Worksheets("Product Details LIVE").PivotTables("PivotTable1").PageFields(1).CurrentPage = "" & p & ""
Sheets("Product Details LIVE").Cells.Copy
Sheets("Product Details").Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Product Details LIVE").Cells.Copy
Sheets("Product Details").Cells.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
[B]Sheets("Product Details").Cells(1, 1).Select[/B]
Sheets("Client Detail LIVE").Cells.Copy
Sheets("Client Detail").Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Client Detail LIVE").Cells.Copy
Sheets("Client Detail").Cells.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
[B]Sheets("Client Detail").Cells(1, 1).Select[/B]
Sheets("Instructions").Activate
ActiveWorkbook.SaveCopyAs Filename:="G:\Groups\New Business\Portfolio Management Matrix\New Macro Generated PMMs\Commercial\PMM - " & p & ".xls"
End If
Next p
End With
End Sub
If you could shed any light on why this error is occuring that would be great!