Hi all,
I have an Excel 2007 sheet sample.xlsx with the following view:
___ A_____B___C___ D___E___F____G___H_
1 title title title title title title title title
2 title title title title title title title title
3 title title title title title title title title
4 title title title title title title title title
5 title title title title title title title title
6 title title title title title title title title
7 title title title title title title title title
8
9 customer1 data data data data data
10 data data data data data data data data
11 data data data data data data data data
12 data data data data data data data data
13 total data data data
14
15 customer2 data data data data data data
16 data data data data data data data data
17 data data data data data data data data
18 total data data data
19
20 ....customer3 ..... ...... ..... ..... ..... ..... ..... ...
total ..... ....... ....... .
....
_______________________________________
So, lines 1-7 are titles following by 'blocks' of data.
At first line of every block (at column B) the word 'customer' always exists.
At last line of every block (at column D) the word 'total' always exists too.
I need a macro to split every block of data (with titles) in a new workbook.
So, the macro should:
1. search the word 'total' from line 8 to bottom
2. if search succeeds, create a new workbook, copy and paste from column A-H, lines 1-7 and lines 8-to line the word 'total' was found e.g. line 13
3. save workbook with filename the value of the cell at column B at first line of the block with the word 'customer1' e.g. cell B9, like 'customer1.xls'
4. go next line e.g. line 14 and search from this line to bottom again
5. if search succeeds, create a new workbook, copy and paste from column A-H, lines 1-7 and line 14-to line the word 'total' was found e.g.line 18
6. save workbook with filename the value of the cell at column B at first line of the block with the word 'customer2' e.g. B15, like 'customer2.xls'
7. do this to the end of sheet creating a workbook for every block of data
Thank you all in advance for any kind of help. I am grateful anyway :-)
Bookmarks