Query during macro run
 

Thank to Kevin Knierim for help with the selection criteria on m
backoder report macro. Now that I have all of our reps together,
want to have the macro query column B (reps name) and insert a blan
row below the name (for all reps), and then sort the sales orders (fo
the same reps) by oldest date. And do it for all reps. EXA: Raw Data

Column B Column F
Rep Name SO Date

John Smith 5/21/04
John Smith 5/3/04
John Smith 5/1/04
John Doe 5/21/04
John Doe 5/3/04
John Doe 5/1/04

After macro run:

Column B Column F
Rep Name SO Date

John Smith 5/1/04
John Smith 5/3/04
John Smith 5/21/04

John Doe 5/1/04
John Doe 5/3/04
John Doe 5/21/0

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

Query during macro run
 

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"mkingsley " wrote in message
...
Thank to Kevin Knierim for help with the selection criteria on my
backoder report macro. Now that I have all of our reps together, I
want to have the macro query column B (reps name) and insert a blank
row below the name (for all reps), and then sort the sales orders (for
the same reps) by oldest date. And do it for all reps. EXA: Raw Data

Column B Column F
Rep Name SO Date

John Smith 5/21/04
John Smith 5/3/04
John Smith 5/1/04
John Doe 5/21/04
John Doe 5/3/04
John Doe 5/1/04

After macro run:

Column B Column F
Rep Name SO Date

John Smith 5/1/04
John Smith 5/3/04
John Smith 5/21/04

John Doe 5/1/04
John Doe 5/3/04
John Doe 5/21/04


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

Query during macro run
 

Try this

Sub SortANdInsert()
Dim i As Long

Application.ScreenUpdating = False
cLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Columns("C:C").Insert Shift:=xlToRight
Range("C2").FormulaR1C1 = "=MID(RC[-1],FIND("" "",RC[-1])+1,999)"
Range("C2").AutoFill Destination:=Range("C2:C" & cLastRow),
Type:=xlFillDefault
Columns("B:F").Sort Key1:=Range("C2"), _
Order1:=xlAscending, _
Key2:=Range("F2"), _
Order2:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Columns("C:C").Delete Shift:=xlToLeft

For i = cLastRow To 2 Step -1
If Cells(i, "B").Value < Cells(i - 1, "B").Value Then
Cells(i, "B").EntireRow.Insert
' i = i - 1
End If
Next i

Application.ScreenUpdating = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"mkingsley " wrote in message
...
Thank to Kevin Knierim for help with the selection criteria on my
backoder report macro. Now that I have all of our reps together, I
want to have the macro query column B (reps name) and insert a blank
row below the name (for all reps), and then sort the sales orders (for
the same reps) by oldest date. And do it for all reps. EXA: Raw Data

Column B Column F
Rep Name SO Date

John Smith 5/21/04
John Smith 5/3/04
John Smith 5/1/04
John Doe 5/21/04
John Doe 5/3/04
John Doe 5/1/04

After macro run:

Column B Column F
Rep Name SO Date

John Smith 5/1/04
John Smith 5/3/04
John Smith 5/21/04

John Doe 5/1/04
John Doe 5/3/04
John Doe 5/21/04


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

Query during macro run
 

Code
-------------------
Sub SortAndInsertSpaces()

Dim iRow As Long
Dim strLast As String

Columns("A:H").Select 'Change this to your columns
Range("A1:H7").Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range( _
"F2"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom
Range("A1").Select

iRow = 2 'Set to your first row of real data
strLast = Range("B" & iRow)
Do Until Range("B" & iRow).Value = ""
If Range("B" & iRow).Value < strLast Then
Range("B" & iRow).EntireRow.Insert
iRow = iRow + 1
End If
strLast = Range("B" & iRow)
iRow = iRow + 1
Loop

End Su
-------------------

Basically, you sort the data first by name then by date, then inser
the rows. The row insert
code simply counts down column B and inserts a new row when the last
value was different
than the current one. I tested this on a small set of data, so I thin
it will work for you once
you make changes to apply to your worksheet (Sort range and star
row).



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