VBA code to export as PDF creating formatting issues

lemondifficult

New Member
Joined
Mar 19, 2014
Messages
40
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

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

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Watch MrExcel Video

Forum statistics

Threads
1,127,199
Messages
5,623,331
Members
415,968
Latest member
Chabal74

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