troubleshooting Question

How to automate the follwoing procedure in MS Excel 2003

Avatar of Coffinated
Coffinated asked on
Microsoft Excel
20 Comments4 Solutions257 ViewsLast Modified:
I need to modify similar CSV in Excel files every day, columns are the same, the only variation is the data itself. When I record a macro it'll work on one file, but not on all of them. Here are the steps I take to modify the CSV file:

1a. Open input.csv file
2a. Sort by column D
3a. Select and copy all rows with "ACC90" In column D
4a. Paste it one line below the block of data

Work with pasted data in the second block:
1. Rename all instances of "ACC90" to "ACC9A" in Column D
2. Copy formulas from another spreadsheet, paste them in first row, column M in the new block
3. Paste columns M-P for all records down
4. Copy columns M,N for all records (row X to 1)
5. Delete values in row M,N in empty line between two blocks of data
6. Select all values in column N (new block)
7. Rename all instances of "B000?" in column N to "B0006"
8. Copy columns M,N and paste as data to columns D,H
9. Copy formulas from column P,Q in the first row of the second block
10. Paste this formula to every row below where either:
- Row in column J has a value
- Row in column P == 0
11. Copy all data in columns P,Q between first and last row in the second block
12. Paste this data as value in first row, column I in the second block
13. Copy all data in column O between first and last row in the second block
15. Paste it as value to first row in column G
16. Delete all entries in columns M-Q
17. Sort all by column B
18. Save as .csv
19. exit

I need the macro to dynamically find correct fields/ranges, for example step #3a may select A1:K9 or A4:k99 etc... Step 4a should find the last row with data and paste the selection two rown down (leaving one empty line between blocks).

input.csv - the original file
final.csv - modified file

Formulas from formula.xls are:
M1=+CONCATENATE(0,F1)
N1=+CONCATENATE("00",H1)
O1=+ROUND(G1/0.15*0.07,2)
P1 - blank
Q1=+ROUND(J1/0.15*0.07,2)

M2=+CONCATENATE(0,F2)
N2=+CONCATENATE("00",H2)
O2=+ROUND(G2/0.15*0.07,2)
P2=+ROUND(I2/0.15*0.07,2)
Q2 - blank

M3,4 N3,4 O3,4 P3,4 are the same as M-P in row 2

Thank you
Sub Test()
'
' Test Macro
' Macro recorded 12/16/2009 by coffinated
'

'
    ChDir "C:\Documents and Settings\coffinated\Desktop"
    Workbooks.OpenText Filename:= _
        "C:\Documents and Settings\coffinated\Desktop\input.txt", Origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
        Array(10, 1), Array(11, 1)), TrailingMinusNumbers:=True
    Columns("C:C").EntireColumn.AutoFit
    Columns("A:K").Select
    Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A1:K23").Select
    Selection.Copy
    Selection.End(xlDown).Select
    Range("A41").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=27
    Range("D41").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "ACC9A"
    Range("D41").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FillDown
    Range("M41").Select
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\coffinated\Desktop\formulas.xls", Origin:= _
        xlWindows
    Range("M1:Q4").Select
    Selection.Copy
    Windows("input.txt").Activate
    ActiveSheet.Paste
    Range("M44:P44").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("M44:P63"), Type:=xlFillDefault
    Range("M44:P63").Select
    Range("M41:N41").Select
    Selection.AutoFill Destination:=Range("M1:N41"), Type:=xlFillDefault
    Range("M1:N41").Select
    ActiveWindow.SmallScroll Down:=-15
    Range("M40:N40").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=9
    Application.WindowState = xlMinimized
    Range("H41:H63").Select
    Selection.Replace What:="B000?", Replacement:="B0006", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("M:M").Select
    Range("M31").Activate
    Selection.Copy
    Columns("F:F").Select
    Range("F31").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("N:N").Select
    Range("N31").Activate
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-45
    Columns("H:H").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=30
    Range("P41:Q41").Select
    ActiveWindow.SmallScroll ToRight:=-7
    Range("O35").Select
    Application.CommandBars("Task Pane").Visible = False
    Range("N42").Select
    ActiveWindow.SmallScroll ToRight:=-2
    Range("P42").Select
    Application.WindowState = xlMinimized
    Application.WindowState = xlMinimized
    Application.WindowState = xlMinimized
    Application.CutCopyMode = False
    Range("P41:Q41").Select
    Selection.Copy
    Range("P46").Select
    ActiveSheet.Paste
    Range("P52").Select
    ActiveSheet.Paste
    Range("P54").Select
    ActiveSheet.Paste
    Range("P56").Select
    ActiveSheet.Paste
    Range("P62").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("P41:Q63").Select
    Selection.Copy
    Range("I41").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("O41").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G41").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("M:Q").Select
    Range("M31").Activate
    Application.CutCopyMode = False
    Selection.ClearContents
    ActiveWindow.LargeScroll ToRight:=-1
    Columns("A:K").Select
    Range("A31").Activate
    Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    ActiveWindow.SmallScroll Down:=-57
    Range("A1").Select
    ActiveWorkbook.Save
    ActiveWindow.Close
    ActiveWindow.Close
End Sub
input.txt
final.txt
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 4 Answers and 20 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 20 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros