Excel_Guy684
New Member
- Joined
- Aug 13, 2021
- Messages
- 7
- Office Version
- 365
- Platform
- Windows
Hi all,
I'm new to Macros and have been trying to set up a macro that groups data, removes filters, sorts data and protects sheets when closing.
All 3 macros work perfectly fine when run on their own but when I close the file and try to run all 3 concurrently it doesn't work with the error 'sub-script out of range' on the sort code in bold.
HELP please.
I'm new to Macros and have been trying to set up a macro that groups data, removes filters, sorts data and protects sheets when closing.
All 3 macros work perfectly fine when run on their own but when I close the file and try to run all 3 concurrently it doesn't work with the error 'sub-script out of range' on the sort code in bold.
HELP please.
Rich (BB code):
Sub EstateTracker()
Dim ws As Worksheet
Dim pwd
pwd = "jason"
Set ws = Sheet1
ws.Unprotect Password:=pwd
If ws.FilterMode Then ws.ShowAllData
If ws.AutoFilterMode Then ws.AutoFilterMode = False
ws.Range("A:D").Ungroup
ws.Range("A:D").Group
ws.Range("V:AS").Ungroup
ws.Range("V:AS").Group
ws.Outline.ShowLevels Columnlevels:=1
Dim lastrow As Long
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
ws.Range("A3:AR" & lastrow).Sort Key1:=Range("G3:G" & lastrow), _
order1:=xlAscending, Header:=xlYes
'ws.Protect'
EnableSelection = xlUnlockedCells
If ws.ProtectContents = False Then
ws.Protect Password:=pwd, AllowFiltering:=True
End If
ActiveWorkbook.Save
End Sub
Sub ServiceEngineers()
Dim ws As Worksheet
Dim pwd
pwd = "jason1"
Set ws = Sheet4
ws.Unprotect Password:=pwd
If ws.FilterMode Then ws.ShowAllData
If ws.AutoFilterMode Then ws.AutoFilterMode = False
ws.Range("A:D").Ungroup
ws.Range("A:D").Group
ws.Range("V:AS").Ungroup
ws.Range("V:AS").Group
ws.Outline.ShowLevels Columnlevels:=1
Dim lastrow As Long
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
ws.Range("A3:AR" & lastrow).Sort Key1:=Range("G3:G" & lastrow), _
order1:=xlAscending, Header:=xlYes
'ws.Protect'
EnableSelection = xlUnlockedCells
If ws.ProtectContents = False Then
ws.Protect Password:=pwd, AllowFiltering:=True
End If
ActiveWorkbook.Save
End Sub
Sub Cadtechnicians()
Dim ws As Worksheet
Dim pwd
pwd = "jason2"
Set ws = Sheet3
ws.Unprotect Password:=pwd
If ws.FilterMode Then ws.ShowAllData
If ws.AutoFilterMode Then ws.AutoFilterMode = False
ws.Range("A:D").Ungroup
ws.Range("A:D").Group
ws.Range("M:AA").Ungroup
ws.Range("M:AA").Group
ws.Range("AF:AH").Ungroup
ws.Range("AF:AH").Group
ws.Range("AL:AO").Ungroup
ws.Range("AL:AO").Group
ws.Outline.ShowLevels Columnlevels:=1
Dim lastrow As Long
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
ws.Range("A3:AN" & lastrow).Sort Key1:=Range("G3:G" & lastrow), _
order1:=xlAscending, Header:=xlYes
'ws.Protect'
EnableSelection = xlUnlockedCells
If ws.ProtectContents = False Then
ws.Protect Password:=pwd, AllowFiltering:=True
End If
ActiveWorkbook.Save
End Sub