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!

Seamus
EE.xlsb
Seamus2626Asked:
Who is Participating?
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.

fjdreherCommented:
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.
0
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

    ActiveWorkbook.RefreshAll


    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
        'unnecessary
        '        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
err_handler:
    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
        Else
            Start = Timer
            'wait 0.5 secs as some graphs do not update quick enough
            Do While Start + 0.1 > Timer
                DoEvents
            Loop
            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

0
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"?

Thanks
Seamus
0
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
0

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.