One way:

Dim ws As Worksheet
Dim wsLatest As Worksheet
Dim sTemp As String
Dim nMax As Long
For Each ws In Worksheets
sTemp = ws.Name
If sTemp Like "Sheet#*" Then
sTemp = Mid(sTemp, 6)
If IsNumeric(sTemp) Then
If CLng(sTemp) nMax Then _
nMax = CLng(sTemp)
End If
End If
Next ws
Set wsLatest = Worksheets("Sheet" & nMax)
ActiveChart.SetSourceData Source:=wsLatest.Range("A5")

In article ,
cesaoes wrote:

this is my line
ActiveChart.SetSourceData Source:=Sheets("Sheet9").Range("A5")
and i keep getting an error becuase, Sheets9 will stay like that, but every
time i run my macro, the sheet number where this is saved will be increasing
the next one will be
sheet10
sheet11....

how do i make it so it understand that has to run the latest one?