macro
 

I have a protect excel spreadsheet that has 12 tabs that people enter in
there children's attendance with o and / they also type in the childrens
names. Is there a way to do a macro, or something that will allow to sort
all the names on all 12 tabs so the children can be in alphabetical order? I
also have formulas written so it calculates o as 1 and / as .5. I hope that
sorting does not interfier with that.
Thanks
Chey

macro
 

You could record a macro that sorts the data the way you want. Then run that
same code for each worksheet that you need sorted.

For example, this expects headers in row 1 of each worksheet. It sorts columns
A:G and determines the last row by the last row used in column A.

And it it uses column 1 (A), column 3 (C), and column 5 (E) as the key columns.

Option Explicit
Sub testme01()

Dim myRng As Range
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
With wks
Set myRng = .Range("a1:G" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With myRng
.Cells.Sort key1:=.Columns(1), order1:=xlAscending, _
key2:=.Columns(3), order2:=xlAscending, _
key3:=.Columns(5), order3:=xlAscending, _
header:=xlYes
End With
Next wks
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Chey wrote:

I have a protect excel spreadsheet that has 12 tabs that people enter in
there children's attendance with o and / they also type in the childrens
names. Is there a way to do a macro, or something that will allow to sort
all the names on all 12 tabs so the children can be in alphabetical order? I
also have formulas written so it calculates o as 1 and / as .5. I hope that
sorting does not interfier with that.
Thanks
Chey

--

Dave Peterson

macro
 

I already have that one down. But what the problem peopel were running into
is wanting to sort between worksheets. Is there a way for that?

"Dave Peterson" wrote:


You could record a macro that sorts the data the way you want. Then run that
same code for each worksheet that you need sorted.

For example, this expects headers in row 1 of each worksheet. It sorts columns
A:G and determines the last row by the last row used in column A.

And it it uses column 1 (A), column 3 (C), and column 5 (E) as the key columns.

Option Explicit
Sub testme01()

Dim myRng As Range
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
With wks
Set myRng = .Range("a1:G" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With myRng
.Cells.Sort key1:=.Columns(1), order1:=xlAscending, _
key2:=.Columns(3), order2:=xlAscending, _
key3:=.Columns(5), order3:=xlAscending, _
header:=xlYes
End With
Next wks
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Chey wrote:

I have a protect excel spreadsheet that has 12 tabs that people enter in
there children's attendance with o and / they also type in the childrens
names. Is there a way to do a macro, or something that will allow to sort
all the names on all 12 tabs so the children can be in alphabetical order? I
also have formulas written so it calculates o as 1 and / as .5. I hope that
sorting does not interfier with that.
Thanks
Chey

--

Dave Peterson

macro
 

You mean that you have data that should switch sheets when you sort all the
data?

If yes, then there's nothing built into excel that'll do that.

In fact, maybe excel isn't the correct tool for the job. If you have lots of
records, maybe using a real database program would be a better solution.

Chey wrote:

I already have that one down. But what the problem peopel were running into
is wanting to sort between worksheets. Is there a way for that?

"Dave Peterson" wrote:


You could record a macro that sorts the data the way you want. Then run that
same code for each worksheet that you need sorted.

For example, this expects headers in row 1 of each worksheet. It sorts columns
A:G and determines the last row by the last row used in column A.

And it it uses column 1 (A), column 3 (C), and column 5 (E) as the key columns.

Option Explicit
Sub testme01()

Dim myRng As Range
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
With wks
Set myRng = .Range("a1:G" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With myRng
.Cells.Sort key1:=.Columns(1), order1:=xlAscending, _
key2:=.Columns(3), order2:=xlAscending, _
key3:=.Columns(5), order3:=xlAscending, _
header:=xlYes
End With
Next wks
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Chey wrote:

I have a protect excel spreadsheet that has 12 tabs that people enter in
there children's attendance with o and / they also type in the childrens
names. Is there a way to do a macro, or something that will allow to sort
all the names on all 12 tabs so the children can be in alphabetical order? I
also have formulas written so it calculates o as 1 and / as .5. I hope that
sorting does not interfier with that.
Thanks
Chey

--

Dave Peterson


--

Dave Peterson