Record a VBA Macro to Format Series of Pivot Charts on Excel

Question

So I've got a bunch of Pivot Charts that I need to format. I'm new to VBA but wanted to record a macro and then use that for each chart. In this code, I want to replace "Sheet 5" with the active chart. Can anyone help me out with that?

Here's the code as it stands:

Sub ChartUp()
' ChartUp Macro AddChartDetails Keyboard Shortcut: Ctrl+Shift+E

ActiveSheet.ChartObjects("Chart 5").Activate
ActiveSheet.ChartObjects("Chart 5").Activate

ActiveChart.Axes(xlValue, xlSecondary).Select
Selection.TickLabels.NumberFormat = "0.00%"
Selection.TickLabels.NumberFormat = "0%"
ActiveChart.Legend.Select
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.ChartArea.Select

ActiveSheet.Shapes("Chart 5").ScaleWidth 1.3668124563, msoFalse, _
    msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 5").ScaleHeight 1.3356401384, msoFalse, _
    msoScaleFromBottomRight

End Sub

Answer 1

I hope I understood your post correctly. I have updated your Sub to receive 2 parameters: Sht As Worksheet and ChtName As String.

Recommendation: I think it's better to stay away from ActiveSheet and instead referenced worksheet, like Worksheets("Sheet1").

I've added another Sub TestChartUp() just to test this solution.

Try this code:

Option Explicit

Sub ChartUp(Sht As Worksheet, ChtName As String)

' ChartUp Macro AddChartDetails Keyboard Shortcut: Ctrl+Shift+E
Dim ChtObj As ChartObject

Set ChtObj = Sht.ChartObjects(ChtName) 

With ChtObj
    .Chart.Axes(xlValue, xlSecondary).TickLabels.NumberFormat = "0%"
    .Chart.Legend.Position = xlLegendPositionBottom
End With

With Sht.Shapes(ChtName )
    .ScaleWidth 1.3668124563, msoFalse, msoScaleFromTopLeft
    .ScaleHeight 1.3356401384, msoFalse, msoScaleFromBottomRight
End With
    
End Sub

'================================================================

Sub TestChartUp()

Call ChartUp(ActiveSheet, "Chart 5") '<-- I prefer not to use ActiveSheet, but "Sheet1" , etc.

End Sub

Edit 1: Running the Sub below when actually selecting the Chart you want to format.

Option Explicit

Sub ChartUp()

' ChartUp Macro AddChartDetails Keyboard Shortcut: Ctrl+Shift+E
Dim Sht As Worksheet
Dim ChtObj As ChartObject

Set Sht = ActiveSheet
Set ChtObj = ActiveChart.Parent

With ChtObj
    .Chart.Axes(xlValue, xlSecondary).TickLabels.NumberFormat = "0.00%"
    .Chart.Legend.Position = xlLegendPositionBottom
End With

With Sht.Shapes(ChtObj.Name)
    .ScaleWidth 1.3668124563, msoFalse, msoScaleFromTopLeft
    .ScaleHeight 1.3356401384, msoFalse, msoScaleFromBottomRight
End With
    
End Sub