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

final.txt

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.txtfinal.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.

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.