Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help with my code below
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 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_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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming | |||
stubborn Excel crash when editing code with code, one solution | Excel Programming |