Runtime Error '9': Subscript Out Of Range

[jack]

Can someone please help and tell me how to correct error message: Runtime
Error '9': Subscript Out Of Range in the following code?
I have marked the line where Debug gives the error with 'ERROR ON THIS LINE
below.
I am trying to run the macro from one workbook and accessing another
workbook which is open in the same folder.
Thanks
Jack

Sub ProtectWkshtsFromSeperateFile()
'Counts worksheets and protects worksheets from passwords
'listed in column B on Passwords worksheet in seperate workbook
Dim ShPassword As String
Dim WkstName As String
Const wkb = "C:\Documents and Settings\Jack\My Documents\@excel password
test\psw source.xlsm"

For i = 1 To Workbooks(wkb).Worksheets.Count 'ERROR ON THIS LINE
WkstName = Worksheets(i).Name
'VLookup sheet password on Passwords worksheet
ShPassword = Application.WorksheetFunction.VLookup(WkstName,
Sheets("Passwords") _
..Range("A1:B" & Sheets("Passwords").Range("B" & Rows.Count).End(xlUp).Row),
2, False)
'Protect each worksheet with specified password on Passwords worksheet
Workbooks(wkb).Worksheets(WkstName).Protect Password:=ShPassword
Next i
End Sub

 

[Jacob Skaria]

Hi Jack

Workbooks(wkb).Worksheets.Count ...........In this code wkb should be the
work book name alone without the full destination. For example
Workbooks("book1.xls") should work. Please try

JacobSkaria
(If this post is helpful please click Yes)

 

[KC]

I would try

For i = 1 To wkb.Worksheets.Count 'ERROR ON THIS LINE

 

[jack]

Thanks Jacob,
Your suggestion worked.
By changing to: Const wkb = "pw source.xlsm"

Now I'm getting the same error message on this line:
Workbooks(wkb).Worksheets(WkstName).Protect Password:=ShPassword
(It's the third to last line of code)
Any suggestions for correcting the above line?
Jack


Hi Jack

Workbooks(wkb).Worksheets.Count ...........In this code wkb should be the
work book name alone without the full destination. For example
Workbooks("book1.xls") should work. Please try

JacobSkaria
(If this post is helpful please click Yes)

 

[Jacob Skaria]

Hi Jack

I dont see any problem with that code; could you please check whether the
password you are retrieving is valid???

JacobSkaria
(If this post is helpful please click Yes)

 

[jack]

Hi Jacob,
Thanks for responding.....
I re-ran the code without changes after posting message and the error on
that line went away!!!
Strange to me!!
Jack


Hi Jack

I dont see any problem with that code; could you please check whether the
password you are retrieving is valid???

JacobSkaria
(If this post is helpful please click Yes)

 

[Jacob Skaria]

Jack, Good to here that!!!!!!!!

If this post is helpful click Yes