Sheets("Sheet9") Error

C

cesaoes

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?
 
J

JE McGimpsey

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")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top