Set X Axis Labels Relative to sheet and last row in range

Question

Goal: Create chart and set X axis labels equal to range in that sheet and last row (value, not formula) in range. The full macro will be looping through each sheet in the wb and creating new graphs in each with data from that sheet.

Full Code:

Sub Test()
Dim Sht As Worksheet
'Create Graph Data: Make Local Mgmt Details Short names
'Create graph
'Size Graph
Dim rng As Range, rngChart As Range, XLabelrng As Range
Dim Lastrowdata As Long
Dim cht As Object

For Each Sht In Worksheets

    'Local Management Details Mapping (graph data)
        With Sht
            .Range("AA8") = "=IFERROR(IF(VLOOKUP(""FX Allocation & Hedging"",B:C,2,FALSE)=0,"""",""FX""),"""")"
            .Range("AB8") = "=IFERROR(IF(VLOOKUP(""FX Allocation & Hedging"",B:C,2,FALSE)=0,"""",VLOOKUP(""FX Allocation & Hedging"",B:C,2,FALSE)),"""")"
            .Range("AA9:AA18") = "=IF(E9=""Yield Curve"",""YC"",IF(E9=""Asset Allocation"",""A. Alloc"",IF(E9=""Security Selection"",""Sec Sel"",IF(E9=""Leverage"",""Lev"",IF(E9=""Intra-Day"",""Intra"",IF(E9=""Pricing Differences"",""Pric"",IF(E9=""Exclusions"",""Exc"",IF(E9=""Interest Rate Derivative Basis"",""IRD"",IF(E9=""Implied Volatility"",""Vol"",IF(E9=""Mortgage"",""Mtg"",IF(E9=""Residual"",""Res"",IF(E9=""Others"",""Others"",""Others""))))))))))))"
            .Range("AB9:AB18") = "=IF(I9="""","""",I9)"
            '.Range("AA8:AB18").Font.Color = vbWhite
        End With
    
    'Your data range for the chart and x-axis labels
        Lastrowdata = [AB:AB].Find("*", , xlValues, , xlByRows, xlPrevious).Row
        Set rng = Sht.Range("AB8:AB" & Lastrowdata)
    
    'Chart Location
        Set rngChart = Range("K9:W18")
    
    'Create a chart (style,XlChartType,Left,Top,Width,Height,NewLayout)
        Set cht = Sht.Shapes.AddChart2(203, xlColumnClustered, 1, 1, 1, 1, False)

    'Chart setup
        With cht.Chart
          .SetSourceData Source:=rng
          .SeriesCollection(1).XValues = ("='" & Sht & "'!$AA$8:$AA" & Lastrowdata)
          .HasTitle = False
          .HasLegend = False
          .Axes(xlValue).MajorUnit = 50
        End With

    'Chart location
        With cht
          .Left = rngChart.Left
          .Top = rngChart.Top
          .Width = rngChart.Width
          .Height = rngChart.Height
        End With

Next Sht

End Sub

Issue: I know I have the syntax wrong here but it should give you a clear idea of what I am looking for: .SeriesCollection(1).XValues = ("='" & Sht & "'!$AA$8:$AA" & Lastrowdata)

When testing in a single sheet, not looping through them I had the code like this .SeriesCollection(1).XValues = "='BMA'!$AA$8:$AA17" to test it, and it all works fine, but I need to be dynamic.

Answer 1

Just missing one thing:

.SeriesCollection(1).XValues = "='" & Sht.Name & "'!$AA$8:$AA" & Lastrowdata