Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatically copying into multiple worksheets (Excel 2003)

    I have been asked to try and do the following and I'm not having any luck.

    What I want is when a line of information is pasted into a spreadsheet, it "looks" at the cell contents of a cell and copies the information into a sheet within that workbook of the same reference.

    It's probably easier looking at an example, so in my attachment Sheet1 is where all of the information is entered from a variety of different sources. What I would like is for it to be sorted from there into individual sheets by the "account reference" in column D where each sheet is named by that reference as shown. The example shown is not very exhaustive but I hope you get the idea.

    There are about 30 Account references so it ends up being a rather large workbook, I have done one file but it all has to be done manually and whilst it is better than what was used before it's still not quite as elegant as it could be.

    Any and all help gratefully received <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  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: Automatically copying into multiple worksheets (Excel 2003)

    Personally, I would not recommend splitting up the data like this. It seems, to me at least, a "poor design" to have multiple copies of the same data. I think the data should only be in 1 place.

    Keeping it in 1 place will allow you to use excel's builtin capabilities (autofilter with subtotals, ptivot tables, etc) to summarize the data.

    For example, using an autofilter, you can filter on col d ( the account reference) and select the account to create essentially the identical info that you want to "separate" without having to separate. Charts can even be built from this "filtered" data so that you don't have multiple charts either.

    I find the autofilter to "intuitive" even for the "less savvy" excel users (even the Marketing managers have been able to figure it out) and is easy to use.

    If you create a macro it could become "cumbersome" to use. You would have to key it on a change event so that even editing an account would create a new reference in the sheet and not delete the old reference (unless you wrote a very complex routine/application).

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically copying into multiple worksheets (Excel 2003)

    That doesn't seem like a very good idea to me. You'll be creating a lot of redundant information, and it'll be difficult to keep it synchronized. You can use AutoFilter or perhaps a pivot table with a page field to be able to select an account reference and display all rows for that reference.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatically copying into multiple worksheets (Excel 2003)

    Is it paramount that this is done in an Excel workbook. I am thinking this type of application could be done in an Access database. As Steve states it is best to keep the data in one place or Hans's suggestion of using the autofilter feature of Excel
    Jerry

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatically copying into multiple worksheets (Excel 2003)

    Further to my last I have attached your original with changes
    Jerry

  6. #6
    New Lounger
    Join Date
    Nov 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically copying into multiple worksheets

    Thanks for all your replies. Following your recommendations I've spent the last couple of hours playing with pivot tables and you are right it's a much easier way of sorting the data. Now just for the joy that is teaching accounts how it works!

    One small question on pivot tables; is there a way of getting it to do a subtotal by month as well as displaying every entry by day? The monthly subtotal then goes into another sheet which collates the total profit/loss and they need to be compared. I can extract this info from the pivot table by using a sumproduct formula, but as soon as the cell references change, it'll make a mess of it.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically copying into multiple worksheets

    You can create a second pivot table based on the same data.
    Right-click the date field in the pivot table. Select Group and Outline | Grouping (may differ slightly depending on the Excel version)
    You can specify whether you want to group by day, month, quarter or year.

  8. #8
    New Lounger
    Join Date
    Nov 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically copying into multiple worksheets

    I can't seem to get it to work for me, I've attached a copy of the sheet I'm working on and when I right click on the date in the pivot table it shows "Group and show detail -> Group, and then it says it can't group that selection. Where am I going wrong?

    Thanks very much for your patience <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically copying into multiple worksheets

    Delete the blank row 3 in Sheet1.
    Delete the sheet with the pivot table, then create it anew.
    You should then be able to group the dates.

  10. #10
    New Lounger
    Join Date
    Nov 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically copying into multiple worksheets

    Cheers for all your help, that's sorted it.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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