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

I'm a bit confused about Steps 6 and 7.
Step 6 is Selecting Column N (Which Contains the Formula =CONCATENATE("00",H1) )
Step 7 is Replacing "B000?" with B0006.
but all the values in Column N would be "00B000x" so would it still replace the B000? with B0006?

bromy2004:
Step 7: Replacing "B000?" with B0006 - there may be different accounts (B0002, B0002...B0005) starting with B000, all of them should be converted to B0006.

The formulas simply add "0" to columns M,N, and calculate percentage of the original value.

I am going to record a short video showing all steps needed to do the process, that should give you an idea of what needs to be done.

change that section to
'Add 0 to Column F
Cells(i, 6).Value = "'0" + Cells(i, 6).Value
'Add 00 to Column H
Cells(i, 8).Value = "'00" + Cells(i, 8).Value

Forgot to change the second one to Cells(i, 8). Sorry bout that

'Add 0 to Column F
Cells(i, 6).NumberFormat = "@"
Cells(i, 6).Value = "0" & Cells(i, 6).Value
'Add 00 to Column H
Cells(i, 8).NumberFormat = "@"
Cells(i, 8).Value = "00" & Cells(i, 8).Value

bromy's code does the same thing by putting the singel quote in front of the 0.....forgot about that one. Always nice to know multiple ways to do things though, just in case.

Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦

Do you use a spreadsheet like Microsoft's Excel? Have you ever wanted to link out to a non excel file on your computer or network drive? This is the way I found to do it!

This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.