Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    distribute data (Excel 2003 SP2)

    A manager gets a workbook with many projects listed. Each project is assigned to one of the staff (about 8 different staff).
    The data is in columns A:M and may cover more than 100 rows. The manager wants to get each staff person to fill in information for their projects and return it to him. I did NOT suggest sharing the workbook as Woody's has many bad things to say about that. The manager currently copies the data assigned to a staff member and pastes it into a new workbook, saving the workbook using the staff persons name. Each of these workbooks are sent to the appropriate staffer so they can fill in the blnaks and return to the manager, who then incorporates all the information into the master workbook. I told him it could be automated, making it faster and less chance of errror.
    Any ideas on techniques/methods?
    A cleaned up, shortened version is attached. Staff names are in Col L.

    Thanks,
    Chuck Reimer
    I'm from the Government and I'm here to help...

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

    Re: distribute data (Excel 2003 SP2)

    It would be possible to write a macro that creates workbooks based on column L by adapting the code in <post:=380,563>post 380,563</post:> or <post:=437,257>post 437,257</post:>.
    And it would also be possible to write a macro that gathers data from returned workbooks.
    But an Access database would be a lot easier to maintain.

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: distribute data (Excel 2003 SP2)

    Hans,
    Thanks for taking the time and for the suggestions. I will look at the posts you suggested and also talk to the manager about using Access.
    Thanks again!
    Chuck Reimer
    I'm from the Government and I'm here to help...

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: distribute data (Excel 2003 SP2)

    Hans,
    I modified your macro SplitSheets to create the sheets based on Column L and also got it to put a header on each new sheet.
    Is there an easy way to have the column widths copied to each new sheet too? I am having trouble putting PasteSpecial Paste:=PasteColumnWidths into the macro.
    Thanks for all your help.
    Chuck Reimer
    I'm from the Government and I'm here to help...

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: distribute data (Excel 2003 SP2)

    Could you show us the code that you have so we can make our recomendation fit into it?
    Legare Coleman

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

    Re: distribute data (Excel 2003 SP2)

    You should use <code>... Paste:=xlPasteColumnWidths</code>

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: distribute data (Excel 2003 SP2)

    Legare,
    It is attached, and thanks!
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  8. #8
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: distribute data (Excel 2003 SP2)

    Hans,
    I tried that but it would not work. I attached the workbook (with the macro) to my reply to Legare.
    Would it be hard to get the macro to save each new sheet to its own workbook, as well as leaving the new sheets in the Master Workbook? The seperate workbooks can be sent to the users for their input, then returned.

    Thanks Hans,
    Chuck Reimer
    I'm from the Government and I'm here to help...

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

    Re: distribute data (Excel 2003 SP2)

    Try the attached code. It exports to new workbooks. You can add the code to create sheets in the workbook again, if you prefer.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: distribute data (Excel 2003 SP2)

    Does the attached work for you?
    Legare Coleman

  11. #11
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: distribute data (Excel 2003 SP2)

    Hans & Legare,
    Thanks to both of you.
    I will take a look and let you know (probably not until tomorrow).
    Chuck Reimer
    I'm from the Government and I'm here to help...

  12. #12
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: distribute data (Excel 2003 SP2)

    Legare & Hans

    My meeting was cancelled so I had time to check out both of your suggestions.
    They both worked GREAT -no suprise there!
    Thanks so much for taking the time.
    Sorry about the tab name on the workbook (was not Master on the last sample I sent in, but the macro expected Master).
    THANKS
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

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