Macro is causing wrong formatting in PDF unless I run two or three times

Question

Hello,

I have a macro that attempts to do the following for about 20 different Excel files:

• Open each file
• Update the links in that file
• Do a single refresh to all pivot tables in that file
• Save the file
• Highlight the relevant sheets and save them as a PDF
• Close the file

Pretty much every time I do this, the PDF has incorrect formatting. These are minor aesthetic issues. A marker on a line graph will appear in the wrong place, for example. The weird thing is that when I run it a second time, the issues disappear. Sometimes it takes three tries, but never more than that.

This is inconvenient, as the macro takes about 30 minutes to run. So I'd like to stop it from happening at all. This is an excerpt from the macro. It's essentially this code repeated 20 times.

   Workbooks.Open Filename:= \_ 

    "\\Team\Finance\Decision Support\Flash Reports\Production Folder\Monthly Department Presentations\Group withCP.xlsx" \_ 

    , UpdateLinks:=3 

ActiveWorkbook.RefreshAll 

ActiveWindow.ScrollWorkbookTabs Sheets:=-1 

ActiveWindow.ScrollWorkbookTabs Sheets:=-1 

ActiveWindow.ScrollWorkbookTabs Sheets:=-1 

ActiveWindow.ScrollWorkbookTabs Sheets:=-1 

ActiveWindow.ScrollWorkbookTabs Sheets:=-1 

ActiveWindow.ScrollWorkbookTabs Sheets:=-1 

ActiveWindow.ScrollWorkbookTabs Sheets:=-1 

ActiveWindow.ScrollWorkbookTabs Sheets:=-1 

ActiveWindow.ScrollWorkbookTabs Sheets:=-1 

Sheets("Feed Slide 14 (K14) B").Select 

ActiveWindow.ScrollWorkbookTabs Sheets:=-1 

Sheets("Feed Slide 12 (K12)").Select 

Range("A27").Select 

ActiveWindow.ScrollWorkbookTabs Sheets:=-1 

ActiveWindow.ScrollWorkbookTabs Sheets:=-1 

ActiveWindow.ScrollWorkbookTabs Sheets:=-1 

ActiveWindow.ScrollWorkbookTabs Sheets:=-1 

ActiveWindow.ScrollWorkbookTabs Sheets:=-1 

Sheets("Feed Slide 4").Select 

ActiveWindow.SmallScroll Down:=3 

ActiveWindow.ScrollWorkbookTabs Sheets:=10 

Sheets("Title Slide").Select 

ActiveWindow.ScrollWorkbookTabs Sheets:=3 

Sheets(Array("Title Slide", "Slide 1", "Slide 2", "Slide 3", "Slide 4", "Slide 5", \_ 

    "Slide 6", "Slide 7", "Slide 8", "Slide 9", "Slide 10", "Slide 11", "Slide 12", \_ 

    "Slide 13", "Slide 14", "Slide 15", "Slide 16", "Slide 17")).Select 

Sheets("Title Slide").Activate 

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= \_ 

    "\\Team\Finance\Decision Support\Flash Reports\Production Folder\Monthly Department Presentations\Group withCP June\_2022.pdf" \_ 

    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas \_ 

    :=False, OpenAfterPublish:=False 

Sheets("Title Slide").Select 

ActiveWorkbook.Save 

ActiveWorkbook.Close 

End Sub

Answer 1

Dear Jad,

As this issue may be related to VBA code, I suggest you post a new thread on Newest 'vba' Questions - Stack Overflow by using the vba tag, along with any other relevant tags. Please note that Stack Overflow has guidelines such as requiring a descriptive title, a complete and concise problem statement, and sufficient details to reproduce your issue. There are also many experienced engineers and experts in the forums over there.

***Disclaimer:***Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

At the same time, we will also keep this thread open, welcome the other community members or experienced experts share your suggestions here.

Sincerely,

Cliff | Microsoft Community Moderator

***Note: In the event that you're unable to reply to this thread, please ensure that your Email address is verified in the Community Website by clicking on Your Account Name > "My Profile" > "Edit Profile" > Add your Email Address > tick "Receive email notifications" checkbox > click on "Save".***