sheet name

[geebee]

hi,

I have the following:

For x = 1 to 30
dim previoussheet as string
previoussheet = "acct" & x-1
set sh = worksheets(previoussheet)

if not sheetexists(sh) then
....

i think there is a problem with the following line:
set sh = worksheets(previoussheet)
because the standard syntax is usually something like:
set sh = worksheets("previoussheet")

i am not sure what to do. could someone help?

thanks in advance,
geebee

 

[JE McGimpsey]

If I understand your question, one way:

Dim sh As Worksheet
Dim x As Long

For x = 1 To 30
On Error Resume Next
Set sh = Worksheets("acct" & x - 1)
On Error GoTo 0
If Not sh Is Nothing Then
' do stuff
End If
Set sh = Nothing
Next x

 

[geebee]

i,
I am getting a "subscript out of range" error pointing to the
Set sh = Worksheets("acct" & x - 1)
line.

geebee

 

[JLGWhiz]

Do you have a sheet named acct0? If not, you will get the error message. I
wondered about that when I saw the x-1. Also make sure the Set statement is
inside the For loop.

 

[JE McGimpsey]

Since the code I gave you has that line right after

On Error Resume Next

which tells VBA to ignore any errors, I'll assume that you've modified
it somehow. Posting the code you actually used may help...