VBA Help

I have a file where on pressing "pdf Country (booked)" the code jumps to slicer "booking region" and selects Asia and then selects "Armenia"  and loops through deselecting all the other countries - as you can imagine this is time consuming.

However if i select Asia manaually and then press "pdf Country (booked)"  the process is much quicker as the code does not need to deselect all countries each time, it loops through nicely

But that means i have to press Asia then press the button then press europe, then press the button - and i have quite a few of these reports

So i would like to be able to press the button - then the code selects Asia - then loop through each country, then select Europe, and loop through etc

Hope this makes sense!

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

what if you add a line Application.ScreenUpdating = False at the beginning of the sub function Step_Thru_Booked_Region().

I noticed at the end it turns it to true, but didnt see the False setting other than in other sub functions.
Roy CoxGroup Finance ManagerCommented:
This should run more smoothly

Sub Step_Thru_Booked_Country()
    Dim slItem As SlicerItem
    Dim i As Long

    Range("U23").Value = "=G3"
    On Error GoTo err_handler
    Application.ScreenUpdating = False
    Call UnhideAllSheets
    'nothing done her
    Call Hide_All

    '  Call Hide_Sheets_Booked


    With ActiveWorkbook.SlicerCaches("Slicer_Booking_Country_Name")
        '--deselect all items except the first
        .SlicerItems(1).Selected = True
        For Each slItem In .VisibleSlicerItems
            If slItem.Name <> .SlicerItems(1).Name Then _
               slItem.Selected = False

        Next slItem

        Call Print_PDF_Booked_Country
        '        Sheets("PCM Sales Manager Dashboard").Activate

        '--step through each item and run custom function
        For i = 2 To .SlicerItems.Count
            .SlicerItems(i).Selected = True
            .SlicerItems(i - 1).Selected = False

            Call Print_PDF_Booked_Country
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

Sub Print_PDF_Booked_Country()
' Print_PDF Macro
    Dim Start As Single
    With Sheets("PCM Sales Manager Dashboard")

        FileMonth = Format(.Range("C23"), "mmm-yy")

        If .Range("C92") = "0" Then
            Exit Sub
            Start = Timer
            'wait 0.5 secs as some graphs do not update quick enough
            Do While Start + 0.1 > Timer
            ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                               "S:\SPM\Horis Info\Horis_Project\GBM\" & FileMonth & "\Output\" & ActiveSheet.Range("K6").Value & " - " & ActiveSheet.Range("E1") & " (" & FileMonth & ") - Booked .pdf" _
                                             , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                                                                                                     :=False, OpenAfterPublish:=False

            'I wouldn't think this is necessary
            '        Sheets("PCM Sales Manager Dashboard").Activate

        End If
    End With
End Sub

Open in new window

Seamus2626Author Commented:
guys i dont understand what is different here

Application.ScreenUpdating = False  - that will not stop the slicer from selecting?

Roy as far as i can see you have just commented out "Sheets("PCM Sales Manager Dashboard").Activate"?

Roy CoxGroup Finance ManagerCommented:
I removed unnecessary selecting and I would also think that the first

Call Print_PDF_Booked_Country

Open in new window

I added Application.ScreenUpdating = False which will not stop the code but will stop the screen having to refresh through out the Loop saving some time

The rest of the code will be slow but I'll check a few things, maybe stop the source calculating each time

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.