Chart Axis range update based on cell values

[rjplante]

I have a 52 week bar chart with data being entered for each week as the data is obtained. I have two cells on the chart sheet and I would like the chart to display the start week and end week as defined by those cell values. I have included my macro and an image of my chart. I don't know how to get my chart to resize the X axis based on the two cells of data (C4 for Start Date and D4 for End Date).

When I run my macro, I get the following error:

Run-time error '-214767259 (80004005)': Method 'MaximumScale' of object 'Axis' failed.

The following line is highlighted when debug is selected:

.MaximumScale = Worksheets("Charts").Range("D4").Value

Macro:

Sub RefreshChart()
 
'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
'The Axis object has one argument, choose between xlCategory, xlSeriesAxis, or xlValue

With Worksheets("Charts").ChartObjects("Chart 21").Chart.Axes(xlCategory)

    'Change maximum scale for Chart 1 on Data Worksheet to value in cell D4
    .MaximumScale = Worksheets("Charts").Range("D4").Value

    'Change minimum scale for Chart 1 on Data Worksheet to value in cell C4
    .MinimumScale = Worksheets("Charts").Range("C4").Value

End With

End Sub

Page Layout:

Sales Tracker 18-MAY-2020.xlsm
	A	B	C	D	E	F	G
1			May	May
2					Quarter
3		Date Range	1-May-2020	31-May-2020
4		Week:	18	23
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Charts

Cell Formulas
Range	Formula
C4:D4	C4	=WEEKNUM(C3)

Cells with Data Validation
Cell	Allow	Criteria
C1:D1	List	=Month
E3	List	=Quarter

Chart: