Dim sSpreadShtNameRng() As String '\Range Name1
Dim sPwdNameRng() As String '\Range Name2
Dim sWriteResPwdNameRng() As String '\Range Name3

No, I don't think I should not be putting() because these are simply
strings. They are not an array It is a string, which is the name of
the
range on Hidden spreadsheet which contains 5 values per range.

My appologies, I grabbed the wrong variables, copy paste thing...

Dim vaSpreadShtName() As Variant
Dim vaPwd() As Variant
Dim vaWriteResPwd() As Variant

ReDim Preserve vaSpreadShtName(1 To 5)
'for dynamic (1 To VarriableHere)
ReDim Preserve vaPwd (1 To 5)
ReDim Preserve vaWriteResPwd(1 To 5)

For iCounter = 1 To 5 Step 1
Debug.Print iCounter
Debug.Print vaSpreadShtName(iCounter)
Debug.Print vaPwd(iCounter)
Debug.Print vaWriteResPwd(iCounter)
Next iCounter

OR...
'using 5 based on the loop to load the array
Dim vaSpreadShtName(5) As Variant
Dim vaPwd(5) As Variant
Dim vaWriteResPwd(5) As Variant

For iCounter = 1 To 5 Step 1
Debug.Print iCounter
Debug.Print vaSpreadShtName(iCounter)
Debug.Print vaPwd(iCounter)
Debug.Print vaWriteResPwd(iCounter)
Next iCounter

HTH--Lonnie M.