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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help with my code below
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help with my code below
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help with my code below
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" wrote: 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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help with my code below
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 |
Reply |
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 |