PDA

View Full Version : [SOLVED] Sort sheets



joanna_gr
04-10-2005, 11:36 AM
Hi all! I hope you're doing fine. I have the following issue. I have a workbook with 16 sheets 8 with forms and 8 with data lists.all the lists are paste links from the original. What I want is a macro to sort all list sheets excluding those with forms. I have to mention that the sheets are not all with data. from a row and after there is number 0. So the data should be filtered to does not equal to 0. Also please not that I need to sort a different column in each sheet. Any idea?

Jacob Hilderbrand
04-10-2005, 11:49 AM
Can you attach the workbook so that we can get a better idea of what you want to do?

joanna_gr
04-10-2005, 09:07 PM
sure. I tried to create a small sample of my workbook and I hope it will help you. The sheet1 is the basic sheet. I have pasted as links all data from the 1st sheet to other two but on the third I added a column. So I want a macro to skip sheet1 and goes to other two and sort them sheet2 by column 1 and sheet3 by col.2. Of course all "0" should be omitted when sorting. I hope is more clear this time.

Killian
04-11-2005, 03:33 PM
I think I understand what you need...
Here's a marco that will sort all the data (it must start in A1) excluding the zeros (these are put at the bottom of the sorted data)


Sub CustomSort()
Dim s As Long
Dim rng As Range
Dim FirstZero
Dim c As Long
For s = 2 To ActiveWorkbook.Sheets.Count
'get all the data starting from cell A1
Set rng = Sheets(2).Range("A1").CurrentRegion
'sort descending (zeros at bottom)
rng.Sort Key1:=Range("B1"), Order1:=xlDescending
'find the range of the first zero
Set FirstZero = rng.Columns(1).Find(What:=0, LookIn:=xlValues)
'count the columns in the range
c = rng.Columns.Count
'reset the range to exclude the zeros
Set rng = Sheets(2).Range(Cells(1, 1), Cells((FirstZero.Row - 1), c))
'resort ascending
rng.Sort Key1:=Range("B1"), Order1:=xlAscending
Next
End Sub

This marco does Worksheet 2 until the last worksheet
If your sheets in your main workbook are alternate (one form, one data, one form, one data, etc, etc) then change the for.. next loop to
For s = 2 To ActiveWorkbook.Sheets.Count Step 2

joanna_gr
04-12-2005, 04:14 AM
well this is not quite what I ask for as the target column is not the same for all sheets. I mean that in sheet 1 the column is B, s3 is C, s4 is B again. So there must be a way to define this.

Killian
04-12-2005, 11:34 AM
I made a mistake wuth the first code - it only sorted sheet 2
here's updated code that uses Select Case to fix which column to sort by. This example just sorts three sheets. You can add which sheets you want to each one using different formats like this:

Case 1 To 4, 7 To 9, 11, 13, Is > 20

So here's the code...


Sub CustomSort()
Dim s As Long
Dim rng As Range, rngKey1 As Range
Dim FirstZero
Dim c As Long
Dim SkipSheet As Boolean
For s = 1 To ActiveWorkbook.Sheets.Count
'get all the data starting from cell A1
Sheets(s).Activate
Set rng = Sheets(s).Range("A1").CurrentRegion
SkipSheet = False
Select Case s
Case 2 ' <---sort these sheets by col B
Set rngKey1 = Range("B1")
Case 3 ' <---sort these sheets by col C
Set rngKey1 = Range("C1")
Case Else ' <---don't any other sheets
SkipSheet = True
End Select
If SkipSheet = False Then
'sort descending (zeros at bottom)
rng.Sort Key1:=rngKey1, Order1:=xlDescending
'find the range of the first zero
Set FirstZero = rng.Columns(2).Find(What:=0, LookIn:=xlValues)
'count the columns in the range
c = rng.Columns.Count
'reset the range to exclude the zeros
Set rng = Sheets(s).Range(Cells(1, 1), Cells((FirstZero.Row - 1), c))
'resort ascending
rng.Sort Key1:=Range("B1"), Order1:=xlAscending
End If
Next s
End Sub

joanna_gr
04-12-2005, 08:17 PM
PERFECT! Thank you very much ... :thumb