"Macro" for splitting data into separate sheets?

    Scenario: Master List, which has all the U.S. states in it.
    What I Need to Do With It: Divide out EACH state's information and put it on a seperate worksheet.


    I've only recently started using macros (to format various documents for easier printing), but now that I'm starting to understand them conceptually, I'm thinking there Should Be some way for me to program a "macro"-type thing to run on the master list and come out with all my state lists, looking beautiful in seperate files.


    Please help me out if you have any clue if this is possible. Thanks!!!!

    Hi - welcome to the forum...


    I'll assume that you want to end up with 51 worksheets in the same workbook - 1 master sheet and 50 individual state sheets. In order to give you some specific direction, a bit more info is needed.


    Is there only 1 row of info per state? What data are you moving to the state sheet. Do you want the macro to create the new sheets also?


    The basic logic would be read through the list of states, create a new sheet with the state name, then do a copy and paste of the info you want to move, loop to the next state, etc until finished.


    Best thing would be to post a copy of your workbook with what you would like it to look like along with some details (use the attachment feature at the bottom of the Post screen (1MB limit on file size)


    .....Ralph

    The rows of info per state vary each time this is done; and I need all the info for each state to be on the separate state sheets, which ideally I would like to be in separate files: not within the same workbook.


    I currently use the copy and paste method, but I'm hoping there's a better way. I'd like the macro to be able to recognize where there is info for Louisiana, cut it out of the master list, and throw it into another file for me.

    Can you post a small example of your workbook so that we can see how your data is arranged. If you have set it up as an Excel Database with column headings, for eg a column headed States, you might be better using a PivotTable to create a report rather than creating 51 separate workbooks.
    If you really want to extract the data to individual workbooks this should not be too difficult using Autofilter via VBA provided your data is correctly set up

    I've started a solution on the attached. On the extract sheet you can pick a state (I've listed only the ones in your example data, but they can be added to and the formulas will adjust) and the data for that state will be extracted for the fields you listed in your example extract. They may be added to as well, but must exactly match the field names in your main data sheet. Dynamic ranges are used so adjustments for additional data are automatic. If you actually want the extracted data for each state to be in a separate workbook, the macro can easily be expanded to cycle through all of the states in the statelist, do the extract, open the state workbook, and replace its contents, with the extracted data. If you need help doing that, post back with the naming convention you are using for the individual workbooks.

    derk, i am now in love with you. as are at least five other people in my office.


    thank you so much.


    i do want to be able to do what you suggested. where naming conventions are concerned, we don't run any strict rules around here seeing as none of us are that familiar with code, etc.


    i really just need to take macro programming, eh?


    thank you for any help...



    :wink1:

    The attached has a macro that will cycle through all of the states in your database and extract the data to a workbook in the same directory called State_XX where XX is the state. Don't open the individual state workbooks yourself before runningthe macro. The macro will open them. If a state workbook didn't exist before, it will be created. Or you can just see the results for an individual state as before. Everything should be self adjusting as you add more data. Any problems, post back.

    Re: "Macro" for splitting data into separate sheets?


    Hi Derk,


    I'm not sure how this works, but do you still have this attachment you created for this last post? Sorry if I'm not doing this correctly, but I think it makes sense to reply to this? I did start a new thread in regards to this, but I think the attachment that you put on this is exactly what I need. I appreciate any help.


    Thank you!
    Tracy

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login