VBA code to export as PDF creating formatting issues

[lemondifficult]

Hi,

I have some code that is intended to run through a list of chart sheet names, PDF'ing each one as it progresses. For some reason I cant get my head around, I have started encountering 2 issues in different scenarios.

Scenario 1

If the file is opened and the macro is run straight away, for some reason when it PDF's the charts it changes the currency format on the y-axis from £ to $. Sometimes it will do it for every chart, sometimes only some charts. When it does do it for only some of the charts, there doesn't seem to be any pattern, it just seems to be random.

It seems to have only started happening since I was allocated a new laptop and since I have been using Excel 2016. I have checked the region settings on the laptop and they are all set to UK with £'s the default currency. I have also checked in Excel and the currency format on the chart is set to £'s, and linked to source which is also set to £'s.

Scenario 2

If the file is opened and I choose a chart and manually print as PDF, then proceed to run the macro, it will PDF each one of the charts with some of the chart being cut off at the right hand margin. The manually printed chart did not have any margin issues.


The issue with encountered in Scenario 1 does not occur in Scenario 2, and the issue in Scenario 2 does not occur in Scenario 1.

Is anyone able to help with this issue? I have posted the VBA code that I am using below. Any suggestions on settings within Excel or Windows that might need changed, or any suggestions to the VBA code itself that would resolve either or both of the issues would be greatly appreciated. Otherwise it looks like I may have to manually PDF 50 or so charts

Thanks in advance.
Micheal

Sub ChartPDF()
    
Dim SheetName As String
Dim FolderFile As String
Dim RowCount As Integer
Dim MaxCount As Integer

    Windows("Energy Price Database.xlsx").Activate
    Sheets("MacroData").Select
    MaxCount = Range("C2").Value

    For RowCount = 1 To MaxCount
     
        Range("B2").Value = RowCount
            Calculate
        SheetName = Range("B4").Value
        FolderFile = Range("H4").Value

        Sheets(SheetName).Select
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            FolderFile, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Sheets("MacroData").Select
        
    Next RowCount
    Range("B2").Value = 1
    
End Sub

 

[lemondifficult]

Just wondering if anyone is able to help with this query? Any suggestions appreciated!

 

[lemondifficult]

Just thought id give this one last bump, hopefully someone can help