+ Reply to Thread
Results 1 to 8 of 8

Loop for search a string, split data, save as new file.

  1. #1
    Registered User
    Join Date
    10-14-2012
    Location
    U.S.A.
    MS-Off Ver
    Excel 2007
    Posts
    8

    Lightbulb Loop for search a string, split data, save as new file.

    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 :-)
    Last edited by johnch; 10-29-2012 at 01:57 AM.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,517

    Re: Loop for search a string, split data, save as new file.

    try
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-14-2012
    Location
    U.S.A.
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Loop for search a string, split data, save as new file.

    Hi jindon,
    Thanks in advance for your direct reply.
    The code works just fine with the attached sample.xlsx

    Unfortunatelly my sample file was not so accurate sample of my real data :-(
    So when the code run with my real data, return a Run-time error '1004': 'The command cannot be used on multiple selections.'.
    Quite expected since my real blocks of data includes more empty cells.
    So, a realistic sample of data is that actual_example.xlsx

    Do you have any idea how your code should be modified in order to work with the actual_example.xlsx?

    Thanks anyway
    Have a nice day

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,517

    Re: Loop for search a string, split data, save as new file.

    I just looked at your actual file and don't feel like writing a code for such a messy data structure.

    Wait for others to come in.

  5. #5
    Registered User
    Join Date
    10-14-2012
    Location
    U.S.A.
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Loop for search a string, split data, save as new file.

    OK jindon thanks for your reply at my first post.

    Hope someone look my second post and have an idea...

  6. #6
    Registered User
    Join Date
    10-14-2012
    Location
    U.S.A.
    MS-Off Ver
    Excel 2007
    Posts
    8

    Thumbs up Re: Loop for search a string, split data, save as new file.

    Hi jindon,

    I finally manage to alter my data structure like my first post (sample.xlsx) in order to use your excellent code.
    Is it possible for you to write just one line of comments for each line of your code to understand how it works?
    It will really help me a lot since I don't want just to copy/paste code but to learn from you.
    Feel free to write any amount of comments you want

    Really thanks,
    Have a nice day

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,517

    Re: Loop for search a string, split data, save as new file.

    According to the data structure, Col.B would be able to get the precise block of data.

    1) Select col.B, hit F5 - Special - Constants will let you select the blocks.
    i.e
    Please Login or Register  to view this content.
    2) Use the first block(Area in vba) of the data will act as a header
    Please Login or Register  to view this content.
    3) Then loop through rest of the blocks(Area) and create new workbook(s).

    Get vba help for Areas, CurrentRegion, Union so that you will understand what it is doing.

  8. #8
    Registered User
    Join Date
    10-14-2012
    Location
    U.S.A.
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Loop for search a string, split data, save as new file.

    Thanks in advanced jindon for your overall responce.
    Really help me so much.

    Have a nice day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1