Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Covert to Multiple Spreadsheets (Excel 2003)

    I have a spreadsheet of data by company . Each company' s identifying code is in column A. There are many companies . I would like to copy each company' s data to a separate spreadsheet so I can do further analysis. The top row above each company is the label row for each column of data. I have attached an example section with the individual identifying data disguised. Can you help?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Covert to Multiple Spreadsheets (Excel 2003)

    The easiest way is probably to extract the unique names (VB using adv filter at runtime), then set up an autofilter (at run time) and loop thru this list, filtering on each and then copying the visible cells into a new worksheet.

    If you need help with this let us know.

    Personally, however, I would not recommend this approach. You can analyze easier in one database by using a pivot table (data - pivot table report). You could put the company name in the page field and get results for each or summarize on all

    If you want "interactive" plotting of particular data, setting up the charts with the full set, then using autofilter (data - filter - autofilter) to view certain criteria is a good approach since only the visible data is plotted. You can also use the SUBTOTAL function to extract summary info of the "visible" data from a filter.

    I also recommend getting rid of the "headers" that are within the data and use use "windows" freeze pane to keep the headers at the top.

    If you break the sections into different sheets, you lose the ability to analyze all the data using the builtin features. (Pivot tables and autofilter with subtotals, being very powerful).

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Covert to Multiple Spreadsheets (Excel 2003)

    <P ID="edit" class=small>(Edited by sdckapr on 23-Jan-05 14:03. Added code to an attachment)</P>I had a chance to work up some code, which (I think) should do what you want...

    Steve

Posting Permissions

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