PDA

View Full Version : Solved: Multiple Page Sort



j19_2002
02-18-2011, 01:39 PM
Hello All,
I have spent the last hour searching for possible solutions to my problem on the forums, but nothing has worked yet so hopefully someone can help me out.

What I need to have happen is sort sheets CBTX all the way to NYB
From B10 across to J10 and all the way down if there are multiple entries.
Sometimes there are no entries in certain sheets but others may have up to 40 or 50. I need it to sort by Branch # Ascending on all the sheets.

I tried recording a macro but came up with a problem when there is no entry and it just cut off.


Please Help!! Thanks

P.S. I have attached a sample worksheet of what I use.

mdmackillop
02-19-2011, 06:16 AM
Option Explicit

Sub Multisort()
Dim sh As Worksheet
Dim LRw As Long
Dim Rng As Range

For Each sh In Worksheets
With sh
LRw = .Cells.Find("*", .Cells(1, 1), , , xlRows, xlPrevious).row
Set Rng = Range(.Cells(10, 2), .Cells(LRw, 2)).Resize(, 9)
If Not Application.CountA(Rng) = 0 Then
Rng.Sort Key1:=Rng(1, 2), Order1:=xlAscending, Header:=xlNo
End If
End With
Next
End Sub

j19_2002
02-19-2011, 09:00 AM
Works flawlessly!! Thank you so much for the help!!

j19_2002
02-22-2011, 01:39 PM
Works flawlessly!! Thank you so much for the help!!

OK, have an error today:

Run-Time Error '91':
Object variable or With block variable not set

and it does it on this line:

LRw = .Cells.Find("*", Cells(1, 1), , , xlRows, xlPrevious).row

mdmackillop
02-22-2011, 03:19 PM
The code fails on a blank sheet. This should catch that error

Option Explicit

Sub Multisort()
Dim sh As Worksheet
Dim LRw As Long
Dim Rng As Range

For Each sh In Worksheets
With sh
If Application.CountA(.Cells) > 0 Then
LRw = .Cells.Find("*", .Cells(1, 1), , , xlRows, xlPrevious).Row
Set Rng = Range(.Cells(10, 2), .Cells(LRw, 2)).Resize(, 9)
If Not Application.CountA(Rng) = 0 Then
Rng.Sort Key1:=Rng(1, 2), Order1:=xlAscending, Header:=xlNo
End If
End If
End With
Next
End Sub

Tinbendr
02-22-2011, 06:30 PM
Shouldn't the second 'cells' be qualified?
LRw = .Cells.Find("*", .Cells(1, 1), , , xlRows, xlPrevious).Row

mdmackillop
02-22-2011, 06:49 PM
Thanks David.
It should, (now corrected) It doesn't seem to affect the functioning though.