Hi Mike and Ed,
Sorry for my slowness here :(( thank you for your help and patience!
I'm still having problems :((( I feel really stupid here.
Basically I have 2 macros, 806X and 807X.

Macro806X just creates a simple bar chart for me. Macro806X works
fine all the time. Macro807X is the 2nd macro after I run 806X. This macro
simply just takes the chart that macro806X created and formats it.

I've listed the code for both macros below.
Also, I can't seem to find the name of the chart by doing CTL+click on the
chart (which is its own worksheet). Not sure what I'm doing wrong.

Sub Macro806X()
'
' Macro806X Macro
'
Cells.Select
Range("A1:AA40").AdvancedFilter Action:=xlFilterInPlace, Unique:=False
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range( _
"'Query18_Subtotal 13wk qty cross'!$1:$1048576")
ActiveChart.ChartType = xlColumnClustered
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=ActiveSheet.Range("A1:R30"), _
PlotBy:=xlRows

ActiveSheet.ChartObjects(1).Activate
ActiveChart.ChartArea.Select
ActiveChart.Location Whe=xlLocationAsNewSheet
ActiveChart.ApplyLayout (5)
ActiveWorkbook.Save
End Sub


Sub Macro807X()
'
' Macro807X Macro
'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Text = "13 Week By Qtry"
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Text = "13 Week By Qty"
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
End Sub