Hello,
I'm trying to sort an array of "Archive" sheets based on the range E3 and N3(down to the end of the sheets) I'm struggling with range and it throws back a debug message.
The array works perfectly for the non archive sheets.
this is the line I don't think it likes
I'm trying to sort an array of "Archive" sheets based on the range E3 and N3(down to the end of the sheets) I'm struggling with range and it throws back a debug message.
The array works perfectly for the non archive sheets.
this is the line I don't think it likes
VBA Code:
Ws.Range("E3:N999999").Sort key1:=Ws.Range("E3"), order1:=xlDescending, Header:=xlYes
VBA Code:
Private Sub Workbook_Open()
Dim Ws As Worksheet
For Each Ws In Sheets(Array("19P1", "21P1", "19P2", "19M1", "19M2", "Spare", "19C1", "19C2", "STN19", "STN21"))
Ws.Unprotect ""
Ws.Range("E4:N14").Sort key1:=Ws.Range("E4"), order1:=xlAscending, Header:=xlYes
Ws.Protect ""
Next Ws
For Each Ws In Sheets(Array("19P1 ARCHIVE", "21P1 ARCHIVE", "19P2 ARCHIVE", "19M1 ARCHIVE", "19M2 ARCHIVE", "Spare ARCHIVE", "19C1 ARCHIVE", "19C2 ARCHIVE", "STN19 ARCHIVE", "STN21 ARCHIVE"))
Ws.Unprotect ""
Ws.Range("E3:N15").Sort key1:=Ws.Range("E3"), order1:=xlDescending, Header:=xlYes
Ws.Unprotect ""
Next Ws
Sheets("overview").Activate
End Sub