Splitting a large file into two and mining the data

[mick02]

Hello,

I've been scouring the Internet for a few days now trying to get a macro working with varying degrees of success. It's driving me mad and I'm really hoping that someone out there can help me out. I'm trying to write a macro that will ...
1. Take a .xls file
2. Split the file in two
3. Take cells B2:B11 and paste them in to a csv file (to be called Upload1.csv) using the rows as column headers (i.e. B2:B11 now become A1:J1)
4. Take cells A13-J13 downwards (this file will have different numbers of rows each time it's generated) and paste them into a different csv file (to be called Upload2.csv) populating all the rows (except all rows in column I which are to be left blank) and all rows in Column J which are to contain the info in cell B7 of the original document.
5. In cell B11 of the original document I must only take the information that says MyAddress:xxxxx and discard all the other info
6. I must also loop through the second document until I find the words Total and copy everything UNTIL that row into the new document.

I know this probably sounds pretty awkward but in practice it's not that difficult to understand, it's probably the way that I've explained it

So far I've managed to take the original document and split it, populate both CSV files but I'm having problems with looping the macro until it sees the word Total and discards it and also populating the appropriate rows with the info from B7 ... I don't know how to make it populate only the rows that have info on them.

I've attached the original Excel file that is generated and also my attempt at the 2 csv files. I've also written in RED what needs to go where in each document.

I'm really really hoping that someone can help me with this as I've been playing around with regular expressions and text to columns and VB Scripts for almost a week and I'm getting kind confused now

Any macro writing gurus out there that can help me???

Thanks in advance,

Mick