Generate & save different files - VBA freezes after 1st step

[AdarsH]

Hello Everyone,

I currently have a piece of code which I am using to:

1) Use filter from a Pivot Table (Customer names/codes)
2) With Filter applied, a statement is generated on another sheet which needs to be saved as values & formats into a new workbook with the file name taken from cells on the sheet.

From what I know, the loop works generally because I have used it for other functions. However, with this ones, it seems to freeze after saving the first file (when filter is on "All").

I would be grateful if you could help me understand and fix this.

Sub ALoop()
 Dim sh As Worksheet
 Dim Pt As PivotTable, Pi As PivotItem
 Dim xPath As String
 Set sh = Worksheets("Data Validation")

xPath = Application.ActiveWorkbook.Path

With sh
     Set Pt = .PivotTables(1)
 End With

Application.ScreenUpdating = False
Application.DisplayAlerts = False

With Pt.PivotFields("Account")
    .ClearAllFilters
    .EnableMultiplePageItems = False
     For Each Pi In .PivotItems
        If Pi.Name <> "(All)" Then
                 
            'Call SveShts
            Worksheets("STATEMENT OF ACCOUNT").Copy
            With Worksheets("STATEMENT OF ACCOUNT").UsedRange
                .Copy
                .PasteSpecial xlValues
                .PasteSpecial xlFormats
            End With
            Application.CutCopyMode = False
            Application.ActiveWorkbook.SaveAs filename:=xPath & "\" & Range("B6").Value & " " & Format(Now(), "DD-MMM-YYYY") & ".xlsx"
            Application.ActiveWorkbook.Close False
           
           
        End If
    Next
End With

    MsgBox "Everything Complete"

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[Micron]

Can only advise that you put a break point at the beginning, trigger the code and step through (F8), watch how it executes and check any variables as you go. I don't know what you mean by "freezes" but I suspect that the problem is masked by turning of alerts. I'd comment that out until solved. Perhaps I'm missing something, but it looks to me like you are trying to save multiple files with the same name in the same location which I would think is not possible.

 

[AdarsH]

Can only advise that you put a break point at the beginning, trigger the code and step through (F8), watch how it executes and check any variables as you go. I don't know what you mean by "freezes" but I suspect that the problem is masked by turning of alerts. I'd comment that out until solved. Perhaps I'm missing something, but it looks to me like you are trying to save multiple files with the same name in the same location which I would think is not possible.

Hello Mircon. The value of cell B6 gets updated when the filter is selected on the Pivot. So, the file name is different for each iteration. By freeze I mean it generates the 1st file & saves it, but then there is no error, macro is running, and keeps running without any output or error messages.

 

[Alex Blakenburg]

I don't know what you mean by freezes either but the way it looks to me, you are not actually "applying" a filter so if it started on All it just stays on that.
Is the field you are filtering on in the Page Filter section or the Row section of the pivot, the syntax is different depending on which one it is ?

VBA - Pivot Table Filter - Automate Excel

 

[Micron]

In the mean time, I had a typo: turning of alerts should be "turning off alerts". Suggest you comment that out and maybe raise an error for a clue. However, I should say I don't think I can offer anything beyond generalities. Alex seems much more cognizant of pivot filters than I.

 

[AdarsH]

Ok. Found what I was doing wrong. I was not actually applying the filter on the pivot field and thus the table would not update. Added the following line in the loop and works now:

Pt.PageFields("Account").CurrentPage = Pi.Name

 

[Alex Blakenburg]

Hmmm. I seem to recollect saying exactly that.

you are not actually "applying" a filter