1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA script that copies multiple lines from Summary into separate tabs.

Discussion in 'VBA Macros' started by Kerny00, Feb 12, 2019.

  1. Kerny00

    Kerny00 New Member

    Messages:
    6
    Hi. I have a spreadsheet that is generated every fortnight. It lists the details of the completed jobs done by our teams(Person A etc). I need to supply each Person with a summary of their work from this list. At the moment it is all done manually and too many errors are being made.

    I would like it to be able to identify the different Person from Col A of the Summary sheet (There may be 1 up to 10(?) at any time) and make a copy of all their jobs and display it in a separate tab. See the SAMPLE tab for an example of the layout. Once each Persons statement has been created, it then removes the "0" columns (the empty columns where there is no quantity).
    Finally, and maybe in addition to this, the final step would be that the Tabs that have been created get exported into their own excel so they can be saved individually. A copy of the original Tab for each Person must stay in the original file too.

    This solution also needs to be a little 'fool-proof' as the person using it will have very limited excel skills.

    Is this possible or do I need to look for another solution?

    Attached Files:

  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,726
    Kerny00 likes this.
  3. Kerny00

    Kerny00 New Member

    Messages:
    6
    Hey thanks, that's awesome. I've adapted the code from the first link.

    This code works if the spreadsheet tab is already set up for that name/ number reference. I'm now wondering if it is possible for the code to use the Company Name as a reference and create the spreadsheet tabs for each Company before it begins the copy and paste....

    Any suggestions would be great!!!
  4. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,726
    Please attach what you've got so far
  5. Kerny00

    Kerny00 New Member

    Messages:
    6
    I've progressed a little further so I've attached the file I'm working off.

    If you use the buttons to get the correct sequence you will see what I am trying to achieve -
    1. Click Step 1 - This macro copies all of the "Completed By" names, copies it to the DATA tab, removes the duplicates so it just leaves only the unique names then it creates a new tab for each of these people.
    2. Extract Statements - copies over the data for each of the people to their own tabs.
    3. Format statements - The TEMPLATE tab is the correctly formatted statement that needs to be copied to each of the tabs that has been created for the people. I've made it to Paste Special - Formulas and Formats only.

    The questions I now have are -
    a. The Step1 macro causes a dialogue box to pop up when removing duplicates. I don't think I can remove this - I am using a MAC - Office2016 - have you heard of this issue?

    b. I require a bit of code to repeat step 3 to format all of the new TABS that have been created. I've only made it to do one on the list. I am struggling with repeating this step.

    c. I would like to merge all of these macros into the 1 so there is only 1 step (plus click yes to the dialogue box). Am I right to just move them into the 1 script and just paste them in one after the other?

    Thanks in advance!!

    Attached Files:

Share This Page