Trying to Sort an array of sheets - VBA

samunders

New Member
Joined
May 26, 2020
Messages
24
Office Version
  1. 2019
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
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
What is the error number & message?
Also when you click debug what line is highlighted?
 

samunders

New Member
Joined
May 26, 2020
Messages
24
Office Version
  1. 2019
Hi Fluff,
Initially it was this line
VBA Code:
      Ws.Range("E3:N999999").Sort key1:=Ws.Range("E3"), order1:=xlDescending, Header:=xlYes

it no longer coming up with an error however its not sorting the data on the array

thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
Do you get any errors?
 

samunders

New Member
Joined
May 26, 2020
Messages
24
Office Version
  1. 2019
No errors or any debug, anymore, everything seems to be working except descending sort for the archive sheets
 

Watch MrExcel Video

Forum statistics

Threads
1,130,118
Messages
5,640,216
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top