Here are the steps I want to automate...I have recorded a macro but here is my problem.

  • Thread starter Thread starter Zarlot531
  • Start date Start date
Z

Zarlot531

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 ?
 
When asking for coding help, always post your efforts for comments
 
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:=xlDatabase,
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
 
OK., The next step is to remove scrolls and all selections as they are not
productive. Example:
Range("A13:F33").ClearContents 'see below on last row
Columns("A:A").Insert Shift:=xlToRight
Range("A1")= 90.2

However, this is not what you asked. Hard to say without seeing the workbook
but you need to determine HOW to determine the rows desired. You can use
something like

lastrowincola=cells(rows.count,"a").end(xlup).row
range("a2:a"&lastrowincola).copy
Your
Range("E9").Select
Range("E9").Cut Destination:=Range("C9")
Range("E11").Select
Range("E11").Cut Destination:=Range("C11")
Range("E13").Select
Click to expand...
could be
for i= 9 to 33 step 2
cells(i,"e").cut destination:=cells(i,"c")
'or
'cells(i,5).cut destination:=cells(i,3)
next i

It appears that you would benefit from professional help on this project.
This is what many of us do.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Zarlot531 said:
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
Click to expand...
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:=xlDatabase,
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

When asking for coding help, always post your efforts for comments

--
Don Guillett
SalesAid Software









- Show quoted text -
Click to expand...
Click to expand...
 
You must log in or register to reply here.

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

AutoFill down a certain number of rows 3
Adding todays date to new records in Excel 6
Playing back a Pivot Table macros 2
here is my code...it's running a bit slow though..any ideas to make it more efficient? thanks 2
Manipulating data 2
The data in a linked Excel spreadsheet column is truncated to 255 characters in an Access database 0
Is it possible with a macro?? 2
Macros for sorting entries 1

Back
Top