One more suggestion. Remove any worksheets that were created by previous
times you ran macro such as mo._commission_rpt (2). There also seems to be
an unamed worksheet that is created.

"Naraine Ramkirath" wrote:

Joel,

thank you. when I run your script, i get this message
"Excel cannot complete this task with available resources. choose less data
or close other applications.
the spreadsheet is very small.

Regards



"Joel" wrote in message
...
I found 3 problems. the code now works

1) I changed the auto fill so it now works and stops at end of range

2) sort range was wrong. You had as a sort key column U but it wasn't
included in the range


3) ther are blank character in your numbers. When they are not numbers
the
show up either center justified or left justified. Number are right
justified. Had to remove
two type spaces. They are asii codes 32 and 160.


Sub Copy_With_AdvancedFilter_To_Worksheets()
Dim CalcMode As Long
Dim ws1 As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim cell As Range
Dim Lrow As Long

Sheets("mo._commission_rpt").Copy Befo=Sheets("mo._commission_rpt")
Set ws1 = Sheets("mo._commission_rpt (2)")

Set rng = ws1.Range("A1").CurrentRegion



Range("O2").Select
ActiveCell.FormulaR1C1 = "=SUMIF(mo_inv_detail_report__1!C[-10]," & _
"mo._commission_rpt!RC[-9],mo_inv_detail_report__1!C)"
Range("O2").Select
Selection.AutoFill Destination:=Range(Selection,
Selection.End(xlDown))
Range("S1").Select
ActiveCell.FormulaR1C1 = "salesrep"
Range("S2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-17],1,2)"
Range("S2").Select
Selection.Copy
Selection.AutoFill Destination:=Range(Selection,
Selection.End(xlDown))
Calculate
Columns("S").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("t1").Select
ActiveCell.FormulaR1C1 = "%"
Range("t2").Select
ActiveCell.FormulaR1C1 = "=round((RC[-5]/RC[-6]*100),2)"
Range("t2").Select
Selection.Copy

'fix this statement
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Selection.AutoFill Destination:= _
Range(Selection, Cells(LastRow, "t"))
Calculate

Columns("t").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'Change t to u
Columns("A:u").Sort Key1:=Range("U2"), Order1:=xlAscending, _
Key2:=Range("B2"), Order2:=xlAscending, Key3:=Range("F2"), _
Order3:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Range("b1").Select
ActiveCell.FormulaR1C1 = "Sales Rep"
Range("j1").Select
ActiveCell.FormulaR1C1 = "State"
Range("k1").Select
ActiveCell.FormulaR1C1 = "ZIP"


With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ws1
rng.Columns("s").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("IV1"), Unique:=True
Lrow = .Cells(Rows.Count, "IV").End(xlUp).Row
.Range("IU1").Value = .Range("IV1").Value

For Each cell In .Range("IV2:IV" & Lrow)
.Range("IU2").Value = cell.Value
Set WSNew = Sheets.Add
On Error Resume Next
WSNew.Name = cell.Value
If Err.Number 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("IU1:IU2"), _
CopyToRange:=WSNew.Range("a1"), _
Unique:=False
WSNew.Columns.AutoFit
WSNew.Rows.AutoFit
Cells.Select

Cells.EntireColumn.AutoFit
Columns("A:t").EntireColumn.AutoFit
Rows("2:2").RowHeight = 13.5
Rows("2:2").Select
Selection.Copy
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:t").Select
Columns("A:t").EntireColumn.AutoFit
Columns("L:O").Select
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Columns("R:R").Select
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"

Next
.Columns("IU:IV").Clear
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


"Naraine Ramkirath" wrote:

I have two worksheets - one detail and one summary.



Here is what I'm trying to achieve: (see my code below. note it is not
working.)



1.. Make a copy of summary sheet called "mo_commission_rpt"
2.. On the summary sheet, I am using the sumif formula to get the
commission amount in column O, based on the criteria of transaction #
(column F). NOTE transaction number on the detail sheet (called
mo_inv_detail_report_1) is column E.
3.. In column S on summary sheet(the copied version), I would like to
get
the first two letter in column B
4.. sort the data based on Column S, B, then F
5.. in column T I would like to compute the % (column 0/column n)
6.. I need to create new sheets based on the unique values in column S
7.. Copy the related data to the respective sheet created in step 5
Here is an excerpt of the two sheets:

Summary:



Territory
Sales Representative
Transaction Date
Customer P/O Date
Customer P/O
Transaction
Customer
Name
City
State or Province
Zip or Postal Code
Total Gross
Total Line Discount
Total Net
Commission Earned
Ship-To
Order Type
Total Header Discount

BLRO
UG
05/08/2007
05/01/2007
100-22601
323526
FRA010U
FRAMEWORKS
BURLINGTON
MA
01803
964.2
0
964.20
144.68

IC
0

BLRO
UG
05/09/2007
04/04/2007
202-3552
4843
PER293U
PER SIMMONS
NORWELL
MA
02061
-15.75
0
(15.75)
(2.36)

IC
0

BLRO
UGE
05/04/2007
03/07/2007
100-22021
323474
HEB120U
HEBREW CENTRE REHAB GIFT SHOP
ROSLINDALE
MA
02131
211.35
0
211.35
31.71

BO
0

BVGE
UV
05/11/2007
03/19/2007
3192007
323841
MUE036U
MUEBLERIA SAVARONA
PUERTO RICO
PR
00725
720
0
720.00
108.00
MAG142U
BO
0

BVGE

UV


05/11/2007
03/26/2007
3232007
323837
JUA274U
JUAN MEDINA
TOA BAJA
PR
00949
2542.05
0
2,542.05
7.49
MAG142U
IC
0