I get Subscript out of Range second time, not the first time

[Pam]

Hello:


I am using a marco to copy two sheet into a workbook from an existing
sheet in same workbook.

I want the user to have the ability to run the marco more than once
which updates the sheet by going to the database, there fore I don't
want the sheets copied more than once..

I check to see if sheet exist, if not I copy it. It works with the
fisrst sheet, but
when I do the test on the second sheet I get subscript out of range.
When I run the macro a second time it works. Its not consistant

What am I doing wrong the logic is the same?




SheetExists = False
On Error GoTo NoSuchSheet
If Len(Sheets("CCB_Host_Analysis").Name) > 0 Then
SheetExists = True
MsgBox ("Sheet already Exist")

End If

NoSuchSheet:
If SheetExists = False Then
Sheets("Sheet1").Copy Before:=Sheets(1)
Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = "LogicalSheet"
Range("C13").Select
MsgBox ("Adding Sheet")
End If



SheetExists = False
On Error GoTo Sheet
If Len(Sheets("3G_Triage_Analysis").Name) > 0 Then
SheetExists = True
MsgBox ("Sheet2 already Exist")
End If


Sheet:
If Sheet2Exists = False Then
Sheets("Sheet1").Copy Before:=Sheets(2)
'Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = "Testsheet"
Range("C13").Select
MsgBox ("No sheet2 exist Adding sheet")
End If

Thank You
Pam

 

[Pam]

Hello:

I am using a marco to copy two sheet into a workbook from an existing
sheet in same workbook.

I want the user to have the ability to run the marco more than once
which updates the sheet by going to the database, there fore I don't
want the sheets copied more than once..

I check to see if sheet exist, if not I copy it. It works with the
fisrst sheet, but
when I do the test on the second sheet I get subscript out of range.
When I run the macro a second time it works. Its not consistant

What am I doing wrong the logic is the same?

SheetExists = False
On Error GoTo NoSuchSheet
If Len(Sheets("CCB_Host_Analysis").Name) > 0 Then
SheetExists = True
MsgBox ("Sheet already Exist")

End If

NoSuchSheet:
If SheetExists = False Then
Sheets("Sheet1").Copy Before:=Sheets(1)
Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = "LogicalSheet"
Range("C13").Select
MsgBox ("Adding Sheet")
End If

SheetExists = False
On Error GoTo Sheet
If Len(Sheets("3G_Triage_Analysis").Name) > 0 Then
SheetExists = True
MsgBox ("Sheet2 already Exist")
End If

Sheet:
If Sheet2Exists = False Then
Sheets("Sheet1").Copy Before:=Sheets(2)
'Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = "Testsheet"
Range("C13").Select
MsgBox ("No sheet2 exist Adding sheet")
End If

Thank You
Pam

Correction, the first logic works for the first sheet but dosen't work
for the second sheet,
I get subscript out of range. Then when I run the macro a second time
it works. I can't delivery macro this way.


HELP PLEASE!!!!!

Thank You
Pam

 

[Tom Ogilvy]

Your problem is probably that your code address whatever workbook is active.
You need to qualify your code so it checks sheets in a particular workbook if
you want consistent behavior. If no other way, you could loop through the
workbooks collection and identify the workbook by some unique feature
perhaps.

 

[Pam]

Your problem is probably that your code address whatever workbook is active.
You need to qualify your code so it checks sheets in a particular workbook if
you want consistent behavior. If no other way, you could loop through the
workbooks collection and identify the workbook by some unique feature
perhaps.

--
Regards,
Tom Ogilvy








- Show quoted text -

Hi:

What I did and it worked, I just split the logic up into to different
fucntions
and it's working fine now. I check if the first sheet exist in the
original funcition
and I moved the checking for second sheet into a different function.

Maybe it didn't like the On Error Go to in the logic twice.



Thank You for your suggestion.
Pam.

 

[Tom Ogilvy]

Upon closer examination of your code, Indirectly that is correct. It
doesn't like it because you never end the error handling and when you get an
error in the error handler, it quits. Separating into separate functions
does cause the error handling to end when the function is exited. but don't
think you couldn't have kept them together with better organization and
correct error handling.