Hi there. I have a sheet w/4 columns. The column is basically sorte
by columns ABD. I would like to add a sheet that allows fo
automatic entry into the other sheet. The code will insert rows int
the other sheet as well as auto sort. Can this be done

--
Message posted from http://www.ExcelForum.com

An example would be the following:

Class Name Symbol Person
A Fidel FI Jo
A Jos J Tank

B Gen G Jo
B Gen G Lank
B Tyc T Nob

C Happ H Dave
C Igl I Eob
C Znn Z Lewis


Etc.Etc. About 10 classes. OK. On another sheet, I would like t
have just a The column headers: Class, Name etc. I would like to b
able to enter in the info and have it moved into its respectiv
position on the other sheet. Order of sort should be Class, then Name
Then Person....no need to worry about symbol. Thank

--
Message posted from http://www.ExcelForum.com

I wouldn't do this. I make too many typos and it would be painful to fix
(mechanically and manually!).

But I would put all the data in one sheet, then on demand create those other
sheets.

In fact, I'd steal code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Look for:

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- creates a list of unique items,
creates a sheet for each item, then replaces old data with current.
AdvFilterCity.xls 46 kb

and

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

"Scottmk <" wrote:

An example would be the following:

Class Name Symbol Person
A Fidel FI Jo
A Jos J Tank

B Gen G Jo
B Gen G Lank
B Tyc T Nob

C Happ H Dave
C Igl I Eob
C Znn Z Lewis

Etc.Etc. About 10 classes. OK. On another sheet, I would like to
have just a The column headers: Class, Name etc. I would like to be
able to enter in the info and have it moved into its respective
position on the other sheet. Order of sort should be Class, then Name,
Then Person....no need to worry about symbol. Thanks

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Thanks Dave,
Although I don't think I'm quite savy enough to modify those codes t
do what I want. I do agree w/that idea though. It would be fine t
have a master sheet that is updated and then a macro that filters. Th
only problem is that those codes seperate what would be on my shee
"class" onto different sheets. Ah...I think I just got a
idea....thanks!! I'll get back to ya

--
Message posted from http://www.ExcelForum.com

Ok..How about this: My sheet has row breaks between each class as show
above. All I am interested in at this point is a macro that will sor
each grouping individually, but in one instance. I believe I have a
absolute reference issue due to the row break. Here is how I wa
planning on recording the macro: Go to first class and Shft+Ctrl dow
then Shift+control Right. That selects my first group. Then-- here'
my problem, absolute referencing the next top left class won't wor
because it won't always be in the same location. If I just push dow
once, will that work....etc etc throughout the rest of the sheet.
Thanks...I guess I should have tried it first, sorry

--
Message posted from http://www.ExcelForum.com

Maybe not what you want, but this is pretty straight forward.

Do it against a copy (or don't save!):

First remove all the page breaks.
second sort all the data based on class. Each class should be grouped together.
Next Data|subtotals.
There's an option there that says to put each group on a separate (printed)
page.

"Scottmk <" wrote:

Ok..How about this: My sheet has row breaks between each class as shown
above. All I am interested in at this point is a macro that will sort
each grouping individually, but in one instance. I believe I have an
absolute reference issue due to the row break. Here is how I was
planning on recording the macro: Go to first class and Shft+Ctrl down
then Shift+control Right. That selects my first group. Then-- here's
my problem, absolute referencing the next top left class won't work
because it won't always be in the same location. If I just push down
once, will that work....etc etc throughout the rest of the sheet.
Thanks...I guess I should have tried it first, sorry.

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Sorry for the confusion: By row break, all I meant is that I skipped a
row inbetween classes. I tried out what I had mentioned and sure
enough, it got screwed up on the reference issue. Basically, I need
code that will be able to start at the top of a range, select all
underneath until there is a blank rowsort itthen go down to the next
group etc etc.
This is what I have so far (which works fine until I insert or delete
rows in the sheet):

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/9/2004 by Scott
'

'
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("B3"), Order1:=xlAscending,
Key2:=Range("D3") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Selection.End(xlDown).Select
Range("A64").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("B64"), Order1:=xlAscending,
Key2:=Range("D64" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Selection.End(xlDown).Select
Range("A88").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("B88"), Order1:=xlAscending,
Key2:=Range("D88" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Selection.End(xlDown).Select
Range("A138").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("B138"), Order1:=xlAscending,
Key2:=Range( _
"D138"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
_
DataOption2:=xlSortNormal
End Sub


---
Message posted from http://www.ExcelForum.com/

First, I wouldn't put that extra row in my data. It makes lots of things just a
little more difficult--including sorts, pivottables, charts, subtotals....

What I would do is either put a border above the first row of each group (or
even just double the rowheight of the the first row of each group. It'll look
double spaced, but not screw up my data.

But if your data in column A is text (not formulas), try this:

Option Explicit
Sub testme()

Dim myArea As Range
Dim myRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No constants"
Exit Sub
End If

For Each myArea In myRng.Areas
With myArea.CurrentRegion
.Sort key1:=.Range("b1"), order1:=xlAscending, _
key2:=.Range("d1"), order1:=xlAscending, _
header:=xlNo, MatchCase:=False
End With
Next myArea
End With

End Sub

If you click on the first cell in a group and hit Edit|goto special|Current
Region, you'll see how it's selecting the range to sort. (Ctrl-* is also a
shortcut for this.)


"Scottmk <" wrote:

Sorry for the confusion: By row break, all I meant is that I skipped a
row inbetween classes. I tried out what I had mentioned and sure
enough, it got screwed up on the reference issue. Basically, I need
code that will be able to start at the top of a range, select all
underneath until there is a blank rowsort itthen go down to the next
group etc etc.
This is what I have so far (which works fine until I insert or delete
rows in the sheet):

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/9/2004 by Scott
'

'
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("B3"), Order1:=xlAscending,
Key2:=Range("D3") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Selection.End(xlDown).Select
Range("A64").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("B64"), Order1:=xlAscending,
Key2:=Range("D64" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Selection.End(xlDown).Select
Range("A88").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("B88"), Order1:=xlAscending,
Key2:=Range("D88" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Selection.End(xlDown).Select
Range("A138").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("B138"), Order1:=xlAscending,
Key2:=Range( _
"D138"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
_
DataOption2:=xlSortNormal
End Sub

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Dave, that code worked perfect! Thanks, and I see what you are sayin
about not having spaces, but I needed them (I thinK). You see,
wanted the first column to stay in a particular order...those were th
groups....and I didn't want them to be alphabetically, so I think tha
is how I had to do it...
Thanks again,
Scot

--
Message posted from http://www.ExcelForum.com

I'm not sure if it's possible, but maybe you could have a helper column (hidden
if you don't want to see it) that contains the real sort order.

An =vlookup() formula that translates the "visible" value into its "hidden"
version.

Value SortValue
abcd 30
aaaa 20
bbbb 40
zzzz 10

=vlookup(a2,hiddensheetname!$a:$b,2,false)

And you could build you own custom list to sort on.



"Scottmk <" wrote:

Dave, that code worked perfect! Thanks, and I see what you are saying
about not having spaces, but I needed them (I thinK). You see, I
wanted the first column to stay in a particular order...those were the
groups....and I didn't want them to be alphabetically, so I think that
is how I had to do it...
Thanks again,
Scott

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson