View Full Version : Looping through worksheets to sort
cmcbeath
04-03-2020, 12:37 PM
I am trying to loop through all sheets in a WB and sort based on Column R decreasing. I have tried multiple different methods from various forums and I continue to get runtime error on my ".Apply". I have attached a sample workbook with a few sheets and a little code, the number of rows changes so i need the range to be dynamic.
Thanks for the help
Paul_Hossler
04-03-2020, 01:07 PM
Try this
BTW your description said "decreasing" but your macro said "xlAscending"
Option Explicit
Sub SortSheetsColR()
Dim ws As Worksheet
Dim r As Range, r1 As Range
Application.ScreenUpdating = False
For Each ws In Sheets
With ws
Set r = .Cells(1, 1).CurrentRegion
Set r1 = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
With .Sort
.SortFields.Clear
.SortFields.Add Key:=r1.Columns(18), Order:=xlAscending
.SetRange r
.Header = xlYes
.Apply
End With
End With
Next ws
Application.ScreenUpdating = True
End Sub
cmcbeath
04-03-2020, 01:12 PM
Sheesh, that would do it. Thank you for the help..
Callum
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.