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)

"jack" wrote:

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