Problem changing visibility in XL 97 1

[AustinMan]

I have a procedure called Copy Range

How it should work is this: On Workbook Close event

Call CopyRange and CopyRange2 (Sheet20 & Sheet21 respectively)

I have to sheets set to xlveryHidden and having difficulty finding the correct syntax to change the Visible property to visible, if I need to.

I am copies ranges from a visible sheet to a hidden sheet.

Here is the Procedure. I am getting Syntax Error 9 Subscript Out of Range

Public Sub CopyRange()
Dim i As Single
Dim Y As Single
Dim Z As Single


Y = 0
Z = 5
Application.EnableEvents = False
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:="DRS Billing"
Worksheets("Sheet20&quot.Visible = -1 (visible = true
For i = 11 To 388 Step 23

Application.Cursor = xlIBeam
Worksheets("Sheet20&quot.Unprotect Password:="2Tough"
Worksheets("Personnel&quot.Unprotect Password:="2Tough"
Worksheets("Personnel&quot.Activate
Range(Cells(i, 1), Cells(Y + 20, 11)).Select
Selection.Copy
Worksheets("Sheet20&quot.Activate
Worksheets("Sheet20&quot.Cells(Z, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues
Z = Z + 10
Y = Y + 23
Next i
Worksheets("Sheet20&quot.Protect Password:="2Tough"
Worksheets("Sheet20&quot.Visible = 2
Worksheets("Section I&quot.Select
Application.Cursor = xlDefault
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Will protect the workbook in a later time.

Any help would be great. Thanks!!

 

[TLady]

Why not to use
Worksheets("Sheet20&quot.Visible = True
Worksheets("Sheet20&quot.Visible = False ??

 

[AustinMan]

I have tried that option and it still gave me the syntax error. I would like it set to xlveryHidden and have also used the constants (xlVisible,xlHidden, XlVeryHidden) with no luck. It seems to be a syntax problem with using the Worksheets collection. Any other suggestions?

 

[sfvb]

I just tried your code and it worked fine on Excel 2000. Are you sure you have the correct names of the sheets?
"Sheet20", "Personnel", and "Section I"

 

[TLady]

Could Protect password be the cause
You Protect Password=True and then trying to make WSheet visible
Comment those and see what will happened

 

[AustinMan]

Yes, It seems to be working now.

Thanks for all of your quick responses.