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

AdarsH

New Member
Joined
Dec 1, 2009
Messages
17
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.


VBA Code:
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,203
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 1, 2009
Messages
17
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

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,629
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 3, 2015
Messages
4,203
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

AdarsH

New Member
Joined
Dec 1, 2009
Messages
17
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:

VBA Code:
Pt.PageFields("Account").CurrentPage = Pi.Name
 

Forum statistics

Threads
1,181,673
Messages
5,931,352
Members
436,787
Latest member
ogharipour

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top