VBA - Lookup or Array - Sheet name, return string

[DKcrm]

Hi

I've made a workbook with 14 different pivot charts - one pivot chart on each sheet. The pivot charts are all based on the same dataset coming out of access (one query feeds all charts - charts are set up manually).

My original problem was that whenever I refreshed the data, all formatting was lost. This is apparently a known MS issue, and the official workaround is to record a macro whilst formatting a chart. I've done that already - All charts to have the same formatting, but the X-axis and chart titles varies from chart to chart.

As you can imagine, my question now is, how can I look up the sheet/chart name, and return a specified value?
So far, I've made input boxes to ask for the names of the Chart title and the X-axis, but an automated solution is greatly preferred.

I can make a lookup table in a different sheet with relevant x-axis names and chart titles per sheet/chart name, but how do I implement such a lookup in my code (shown below).
* I've also heard of an 'Array' solution to do this, but how do I make this in code?

Sub ChgPivotChartFormat()
'
'
' Create text for the Chart Header & X-axis
ChartTitle = InputBox("Please enter title of the chart", "Pivot Chart Title")
XAxisDim = InputBox("What are the X Axis dimension? 'WEEK - RDC' or 'WEEK - BRANDS'", "Pivot Chart - X Axis dimension")


    ActiveChart.ChartArea.Select
    ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Line - Column on 2 Axes"
    ActiveChart.Legend.Select
    Selection.Position = xlTop
    ActiveChart.PlotArea.Select
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = ChartTitle
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = XAxisDim
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Days"
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Pcs"
    End With
    With ActiveChart.Axes(xlCategory)
        .HasMajorGridlines = False
        .HasMinorGridlines = False
    End With
    With ActiveChart.Axes(xlValue)
        .HasMajorGridlines = True
        .HasMinorGridlines = False
    End With
    ActiveChart.PlotArea.Select
    With Selection.Border
        .ColorIndex = 16
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Fill.PresetGradient Style:=msoGradientHorizontal, Variant:=1, _
        PresetGradientType:=msoGradientFog
    Selection.Fill.Visible = True
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    With Selection.Border
        .ColorIndex = 2
        .Weight = xlHairline
        .LineStyle = xlContinuous
    End With
    ActiveChart.SeriesCollection(2).Select
    With Selection.Border
        .ColorIndex = 41
        .Weight = xlMedium
        .LineStyle = xlContinuous
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlNone
        .MarkerForegroundColorIndex = xlNone
        .MarkerStyle = xlNone
        .Smooth = False
        .MarkerSize = 5
        .Shadow = False
    End With
    ActiveChart.SeriesCollection(1).Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlAutomatic
    End With
    Selection.Shadow = True
    Selection.InvertIfNegative = False
    With Selection.Interior
        .ColorIndex = 11
        .Pattern = xlSolid
    End With
End Sub

Example of sheet and x-exis & chart title relations:
Sheet/Chart;X-Axis;ChartTitle
h_s_sp;'Week';'Stock Service Product'
f_s_co;'Week';'Collection'
h_s_co_pol;'Brands';'Collection'