| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here are the steps I want to automate...I have recorded a macro but here is my problem.
First, let me describe what I want to automate and also what I've been
able to accomplish with the recorder. Step 1 Every day, I have a text file full of A/R numbers, G/L account numbers, and dollar amounts in three columns. I paste this text file into Excel, go text-to-columns, never requiring adjustment in the text- to-columns window. I then sort by G/L account number and remove unwanted G/L account numbers and also subtotal amounts and other text that ends up at the bottom -- it is unneeded for the pivot table I will eventually do. Then, I do the same thing with the prior date's text file. I then run a pivot table to see which A/R numbers have changed. I have been able to record a macro that will run the pivot table for a given two days. THe problem is that I don't know how to delete the "junk" that is at the bottom of the sort dynamically at ANY given day. I also don't know how to select dynamically since some days have more data (and thus more rows) than other days. Is there any good learning tool out there for learning how to do this? Could any of you help me ? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Here are the steps I want to automate...I have recorded a macro but here is my problem.
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Here are the steps I want to automate...I have recorded a macro but here is my problem.
Ok, here is the code. Note that I have deleted many
ActiveWindow.ScrollRow = XXX that is a result of the recorder recording me moving throught the spreadsheet. This macro works for the given days I recorded. My problem is that I want to be able to automate this process for any given days, with any number of rows. You also see where I deleted the "junk" that resulted at the top or bottom from my "sort." I would like to automate this deletion process. Thanks. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 3/21/2007 by Joshua ' ' Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(25, 1), Array(43, 1), Array(63, 1), Array(67, 1), _ Array(76, 1)), TrailingMinusNumbers:=True Columns("A:A").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("B:C").Select Selection.Delete Shift:=xlToLeft Columns("A:G").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A13:F33").Select Selection.ClearContents Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A1").Select ActiveCell.FormulaR1C1 = "90.2" Range("A1").Select Selection.AutoFill Destination:=Range("A1:A12") Range("A1:A12").Select Range("C7").Select Sheets("Sheet2").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(25, 1), Array(43, 1), Array(63, 1), Array(67, 1), _ Array(76, 1)), TrailingMinusNumbers:=True Columns("A:A").Select Selection.Delete Shift:=xlToLeft Columns("B:D").Select Selection.Delete Shift:=xlToLeft Columns("A:F").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A13:E34").Select Selection.ClearContents Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A1").Select ActiveCell.FormulaR1C1 = "90.3" Range("A2").Select Sheets("Sheet2").Select Range("A1").Select Selection.AutoFill Destination:=Range("A1:A12") Range("A1:A12").Select Range("C9").Select Sheets("Sheet3").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(20, 1), Array(34, 1), Array(42, 1), Array(52, 1), _ Array(54, 1), Array(63, 1), Array(76, 1), Array(86, 1), Array(108, 1)), _ TrailingMinusNumbers:=True Range("A:A,C:C,D:D,E:E,F:F,G:G").Select Range("G1").Activate Selection.Delete Shift:=xlToLeft Columns("A:H").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("E22").Select ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 1 Range("A1:H15").Select Range("H15").Activate Selection.ClearContents Selection.Delete Shift:=xlUp Range("O18").Select ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 509 Range("A523:M1049").Select Selection.ClearContents Columns("A:G").Select Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A1:H158").Select Selection.Delete Shift:=xlUp Range("A35:I522").Select Selection.Delete Shift:=xlToLeft Range("E1").Select ActiveCell.FormulaR1C1 = "=-1*RC[-1]" Range("E1").Select Selection.Copy Range("C1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("C3").Select ActiveCell.FormulaR1C1 = "=-1*RC[1]" Range("C3").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("D1:E4").Select Selection.ClearContents Range("D5").Select Range("D5").Cut Destination:=Range("C5") Range("C5").Select ActiveCell.FormulaR1C1 = "-120.26" Range("D7").Select Range("D7").Cut Destination:=Range("C7") Range("C7").Select ActiveCell.FormulaR1C1 = "-24.5" Range("E9").Select ActiveCell.FormulaR1C1 = "=-1" Range("E9").Select ActiveCell.FormulaR1C1 = "=-1*RC[-1]" Range("E9").Select Selection.AutoFill Destination:=Range("E9:E34") Range("E9:E34").Select Range("E9").Select Range("E9").Cut Destination:=Range("C9") Range("E11").Select Range("E11").Cut Destination:=Range("C11") Range("E13").Select Range("E13").Cut Destination:=Range("C13") Range("E15").Select Range("E15").Cut Destination:=Range("C15") Range("E19").Select Range("E19").Cut Destination:=Range("C19") Range("E21").Select Range("E21").Cut Destination:=Range("C21") Range("E17").Select Range("E17").Cut Destination:=Range("C17") Range("E23").Select Range("E23").Cut Destination:=Range("C23") Range("E25").Select Range("E25").Cut Destination:=Range("C25") Range("E27").Select Range("E27").Cut Destination:=Range("C27") Range("E29").Select Range("E29").Cut Destination:=Range("C29") Range("E31").Select Range("E31").Cut Destination:=Range("C31") Range("E33").Select Range("E33").Cut Destination:=Range("C33") Range("C1:C34").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("D4:G36").Select Selection.ClearContents Range("E13").Select Sheets("Sheet3").Select Range("A1:C34").Select Selection.Copy Sheets("Sheet1").Select Range("A13").Select Sheets("Sheet3").Select Application.CutCopyMode = False Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A1").Select ActiveCell.FormulaR1C1 = "53" Range("A1").Select Selection.AutoFill Destination:=Range("A1:A34") Range("A1:A34").Select Range("A1:C34").Select Range("C34").Activate Selection.Copy Sheets("Sheet1").Select Range("A13").Select ActiveSheet.Paste Range("C27").Select Sheets("Sheet2").Select Range("A1:C12").Select Range("C12").Activate Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select Range("A47").Select ActiveSheet.Paste Rows("1:1").Select Application.CutCopyMode = False Selection.Insert Shift:=xlDown Range("A1").Select ActiveCell.FormulaR1C1 = "Report" Range("B1").Select ActiveCell.FormulaR1C1 = "A/R" Range("C1").Select ActiveCell.FormulaR1C1 = "Amount" Columns("A:C").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!A1:C59").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").RowGrand = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="A/R", _ ColumnFields:="Report" With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Amount") .Orientation = xlDataField .Caption = "Sum of Amount" .Function = xlSum End With End Sub On Mar 17, 12:49 pm, "Don Guillett" wrote: When asking for coding help, always post your efforts for comments -- Don Guillett SalesAid Software "Zarlot531" wrote in message ps.com... First, let me describe what I want to automate and also what I've been able to accomplish with the recorder. Step 1 Every day, I have a text file full of A/R numbers, G/L account numbers, and dollar amounts in three columns. I paste this text file into Excel, go text-to-columns, never requiring adjustment in the text- to-columns window. I then sort by G/L account number and remove unwanted G/L account numbers and also subtotal amounts and other text that ends up at the bottom -- it is unneeded for the pivot table I will eventually do. Then, I do the same thing with the prior date's text file. I then run a pivot table to see which A/R numbers have changed. I have been able to record a macro that will run the pivot table for a given two days. THe problem is that I don't know how to delete the "junk" that is at the bottom of the sort dynamically at ANY given day. I also don't know how to select dynamically since some days have more data (and thus more rows) than other days. Is there any good learning tool out there for learning how to do this? Could any of you help me ?- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Here are the steps I want to automate...I have recorded a macro but here is my problem.
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| changing a recorded macro - date problem.... | Excel Programming | |||
| Unique problem with recorded macro | Excel Programming | |||
| Problem with a recorded macro to create two pivot tables from same data | Excel Programming | |||
| Problem with a recorded macro to create two pivot tables from same data | Excel Programming | |||
| Steps from Macro recorder for Pivot table will not run as a Macro | Excel Programming | |||
Linear Mode
