please help with my code below

N

Naraine Ramkirath

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

BVGE
UVA
05/11/2007
03/07/2007
EMAIL 03/07
323839
MAY100U
MAYACAN
SAN JUAN
PR
00926
875.1
0
875.10
131.27
MAG333U
BO
0

BVGE
UVA
05/11/2007
02/26/2007
EMAIL 02/26
323830
CAS167U
CASA LUEVAS
BAYAMON
PR
00956
295.35
0
295.35
44.30
MAG333U
BO
0

BVGE
UVA
05/09/2007
02/26/2007
EMAIL 02/26
323627
CAS167U
CASA LUEVAS
BAYAMON
PR
00956
941.25
0
941.25
141.19
MAG333U
BO
0

CAND
UK
05/15/2007
05/04/2007
14327
323860
JEN033U
USE JEN033U
MINSTER
OH
45865
365.4
0
365.40
54.82
JEN340U
IC
0

CAND
UK
05/15/2007
03/07/2007
14625
323856
COT242U
COTTAGE ON THE RIVER
GRAND RAPIDS
MI
43522
612
0
612.00
91.80

IC
0

CAND
UK
05/11/2007
04/13/2007
14258
323829
BER475U
BERLIN VILLAGE GIFT BARN LTD
BERLIN
OH
44610
299.4
0
299.40
44.91

BO
0

CAND
UK
05/04/2007
04/27/2007
13391
323475
HOM225U
HOME ESSENTIALS
TRAVERSE CITY
MI
49684
141.75
0
141.75
21.26

IC
0

CAND
UK
05/08/2007
04/13/2007
14258
4835
BER475U
BERLIN VILLAGE GIFT BARN LTD
BERLIN
OH
44610
-55.15
0
(55.15)
(8.27)

IC
0

CAND
UKA
05/11/2007
05/03/2007
FAX 05/03
323833
DECORU
DECORATING SCENE LLC
ALLEBDALE
MI
49401
228.8
0
228.80
34.32

DIR
0




Detail:

Territory
Sales Representative
Transaction Date
Customer P/O
Transaction
Customer
Name
City
State or Province
Zip or Postal Code
Item
Description 1
Quantity Shipped
Commissionable Value
Commission Earned
Total Header Discount
Total Line Discounts
Commission Group

NORT
UN
05/01/2007
SH030207RS
4819
SCH141U
SCHEELS DESIGN STUDIO
FARGO
ND
58103-3403
EI810BR
ELINE-8X10 STITCHED PU FRAME-CHOCOLAT
0
-6.3
-0.95
0
0
FRAM

NORT
UN
05/01/2007
SH030207RS
4817
SCH141U
SCHEELS DESIGN STUDIO
FARGO
ND
58103-3403
TR2013B
TRADITIONS-20X13 SUSPENSE FRIENDS-BLACK
-2
-31.5
-4.73
0
0
FRAM

NORT
UN
05/01/2007
SH030207RS
4817
SCH141U
SCHEELS DESIGN STUDIO
FARGO
ND
58103-3403
VN13WE
VINITO HOLDS 13 BOTTLES - ESPRESSO
-1
-26.25
-3.94
0
0
FRAM

CAND
UKF
05/02/2007
12387
4822
LIL569U
LILY'S ON MAIN
SYLVANIA
OH
43560
ENM024
ELAN MIRROR ROUND 24in DIAMETER
-1
-23.65
-3.55
0
0
FRAM

DKRY
UFC
05/02/2007
FAX 04/05
4821
VIL255U
VILLAGE GALLERY
STROMSBURG
NE
68666
105003
AUTOGRAPH - SLEEPOVER
-3
-12.6
-0.63
0
0
DISC

DKRY
UFC
05/02/2007
FAX 04/05
4821
VIL255U
VILLAGE GALLERY
STROMSBURG
NE
68666
FC03AP
FONT COLLAGE ANGEL13X15 PINK
-3
-47.25
-7.09
0
0
FRAM

DMS
UNA
05/02/2007
0001309
4820
GOO714U
GOOD S OF EVANSTON
EVANSTON
IL
60202
771025T
LINEN GIFT SET - PERSIMMON
-6
-45
-6.75
0
0
FRAM

BLRO
UGE
05/04/2007
100-22021
323474
HEB120U
HEBREW CENTRE REHAB GIFT SHOP
ROSLINDALE
MA
02131
MU33G
MUSE-PU TEALIGHT HOLDER - WASABI
6
45
6.75
0
0
PRO

BLRO
UGE
05/04/2007
100-22021
323474
HEB120U
HEBREW CENTRE REHAB GIFT SHOP
ROSLINDALE
MA
02131
MU33O
MUSE-PU TEALIGHT HOLDER - SIENNA
6
45
6.75
0
0
PRO

BLRO
UGE
05/04/2007
100-22021
323474
HEB120U
HEBREW CENTRE REHAB GIFT SHOP
ROSLINDALE
MA
02131
NQ06LI
QUARTZ - LIVE LOVE LAUGH 4X6
3
26.85
4.03
0
0
FRAM

BLRO
UGE
05/04/2007
100-22021
323474
HEB120U
HEBREW CENTRE REHAB GIFT SHOP
ROSLINDALE
MA
02131
TR1813B
TRADITIONS-20X13 SUSPENSE FAMILY-BLACK
3
47.25
7.09
0
0
FRAM

BLRO
UGE
05/04/2007
100-22021
323474
HEB120U
HEBREW CENTRE REHAB GIFT SHOP
ROSLINDALE
MA
02131
TR2013B
TRADITIONS-20X13 SUSPENSE FRIENDS-BLACK
3
47.25
7.09
0
0
FRAM

CAND
UK
05/04/2007
13391
323475
HOM225U
HOME ESSENTIALS
TRAVERSE CITY
MI
49684
PI235B
PINOT-WINE GLASS SHELF-BLACK
3
63
9.45
0
0
FRAM

CAND
UK
05/04/2007
13391
323475
HOM225U
HOME ESSENTIALS
TRAVERSE CITY
MI
49684
RS219J
VINTAGE SHELF JAVA
3
78.75
11.81
0
0
FRAM

CURL
UD
05/04/2007
07-8180
323476
ISL614U
ISLANDER FLAGS
KITTY HAWK
NC
27949
L104E&
FONT 4in SYMBOL & EBONY
6
7.5
1.13
0
0
FRAM




my code:



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 Before:=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_deta
il_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
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
Calculate

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

Columns("A:t").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
 
J

Joel

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 Before:=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
 
N

Naraine Ramkirath

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 said:
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 Before:=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 said:
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

BVGE
UVA
05/11/2007
03/07/2007
EMAIL 03/07
323839
MAY100U
MAYACAN
SAN JUAN
PR
00926
875.1
0
875.10
131.27
MAG333U
BO
0

BVGE
UVA
05/11/2007
02/26/2007
EMAIL 02/26
323830
CAS167U
CASA LUEVAS
BAYAMON
PR
00956
295.35
0
295.35
44.30
MAG333U
BO
0

BVGE
UVA
05/09/2007
02/26/2007
EMAIL 02/26
323627
CAS167U
CASA LUEVAS
BAYAMON
PR
00956
941.25
0
941.25
141.19
MAG333U
BO
0

CAND
UK
05/15/2007
05/04/2007
14327
323860
JEN033U
USE JEN033U
MINSTER
OH
45865
365.4
0
365.40
54.82
JEN340U
IC
0

CAND
UK
05/15/2007
03/07/2007
14625
323856
COT242U
COTTAGE ON THE RIVER
GRAND RAPIDS
MI
43522
612
0
612.00
91.80

IC
0

CAND
UK
05/11/2007
04/13/2007
14258
323829
BER475U
BERLIN VILLAGE GIFT BARN LTD
BERLIN
OH
44610
299.4
0
299.40
44.91

BO
0

CAND
UK
05/04/2007
04/27/2007
13391
323475
HOM225U
HOME ESSENTIALS
TRAVERSE CITY
MI
49684
141.75
0
141.75
21.26

IC
0

CAND
UK
05/08/2007
04/13/2007
14258
4835
BER475U
BERLIN VILLAGE GIFT BARN LTD
BERLIN
OH
44610
-55.15
0
(55.15)
(8.27)

IC
0

CAND
UKA
05/11/2007
05/03/2007
FAX 05/03
Click to expand...
Click to expand...
 
J

Joel

I did a seqarch for this error message on this website. found a few hits
similar to your problem. Nothing seems to corrolate to your problem

Since I ran this code on my PC with your data. (It took a while to get the
data your posted into my spreadsheet). I think it must have something to do
with the data. I removed the spaces because I saw error messages in the
spreadsheet.

Look through all you worksheets and see if there arre any error messages in
the cells. I found these errors were caused by spaces in the data. Remove
the spaces if possible.

Joel said:
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 Before:=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 said:
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

BVGE
UVA
05/11/2007
03/07/2007
EMAIL 03/07
323839
MAY100U
MAYACAN
SAN JUAN
PR
00926
875.1
0
875.10
131.27
MAG333U
Click to expand...
Click to expand...
 
J

Joel

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 said:
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 said:
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 Before:=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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Macro Code Help 9
Help with code please 4
Help wanted with Code, Please 6
Help with Totals Please 13
Help with a Formula Within a Function Please 5
How to find a specific inform with some cells and return total 0
Help with shortening/cleaning some code please 3
Help with my code Please 1

Top