Split data from a master document into various worksheets based on a template sheet

{ 22 Comments }

By Ashish Mathur · · · Leave a comment

Assume a base data sheet with information for various vendors.  There is also a worksheets which has the template of the reconciliation statement which is sent to all vendors.  At the click of a button, one may want vendor worksheets to be created from the template.

Furthermore, one may want to do either of the following:

1. Create sheets for all vendors at once;

2. Create individual sheets selected by the end user

You may refer to my solution in this workbook.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • thank you so much sir!

    but i want the vendor number (start with htl) in the drop box list.. i was trying to adjusting the columns but not able to do this
    and i just want to create a reports for all vendors(no restrictions)

    please help me .
    please advice

    regards
    sanjay

    Reply

      • hi sir,

        every thing is fine.. but i can want to remove the restriction for vendor sheets..

        if i click on creates all vendros . it goes up to only 10 sheet not all 1300 vendors…

        please advice how to do it

        Reply

        • Hi,

          In the code, there is line which is For n = 7 To 10. Change this to For n = 7 To Range("I65000").End(xlUp).Row

          Also, please note that I have not tested it for 1300 sheets so it may either take a lot of time or MS Excel may hang. Try it at your own risk.

          Reply

          • thanks sir!

            i have used the bleow code but i m getting only 23 worksheets..

            For n = 3000 To 100000 ‘Range(“I65000”).End(xlUp).Row.

          • Hi,

            You may refer to my solution in this this workbook.

            As mentioned earlier, I have not tested it for 1300 sheets so it may either take a lot of time or MS Excel may hang. Try it at your own risk

  • thank you so much sir!,, it’s perfect

    let suppose I just want Vendor Code instead of vendor name in Drop down list …..

    I want to send invoice by selecting vendor codes, so If I suppose change the columns of vendor codes and Name…
    will It work the same for Vendor Codes as well??

    Reply

  • hi sir,

    sorry to bother again!. but i checked one thing here..
    when ever i change my data (like i have to add up or reduced rows)..it doesn’t work on “create all sheet”..

    i got every time the below mention bug.. please advice.
    Set rng2 = Worksheets(“raw data”).Range(“A1”).Offset(1, 0).Resize(rng.Rows.Count – 2, 11).SpecialCells(xlCellTypeVisible)

    regards/sanjay

    Reply

  • Hi Sir,
    I am working on a enquiry tracking sheet which has Yearly business enquiry list. I am trying to copy data from yearly master sheet to individual sheets date wise i.e Jan sheet has all Jan enquiries, Feb has all Feb enquiries and son. I was manually doing this but it takes a lot of time. Kindly could you let me know how i can modify the above macro to suit my needs.

    I have uploaded the sample sheet
    https://www.dropbox.com/s/x8spcw15uq08vkl/sample_Enquiry_tracker.xls

    Reply

    • Hi,

      You may refer to my solution in this file. To get data for Feb, try this

      1. Right click on the Jan tab and select Move or Copy
      2. Check the box for Create a Copy and click on OK
      3. Rename the newly created tab to Feb

      The Feb sheet will now show data for the month of February.

      Hope this helps.

      Reply

  • Dear Ashish,

    *** How do I copy the precise cell data from row one/then two/ then row three, on repeat, from sheet 1 to multiple separate sheets if the cells on the destination sheet/s are scattered (in other words, I cannot drag drop) ***

    I would like to extract the information in the cells (some are numbers, others are the “words and some are words and number combinations” my the table in sheet 1 to another sheet . I need to do this row by row, (row one is a header) to random /scattered cells, into another existing Excel spreadsheet in sheet 2. If I cannot do this in a repeat fashion (row by row to the same sheet) , so that sheet two automatically updates to the next row’s information in sheet 1’s; then I need to duplicate the contents of sheet 2 as a template to multiple sheets, and extract the information in sheet 1’s rows, individually row by row to these scattered/random cell locations to multiple consecutive sheets in order to print them as individually numbered workshop job cards.

    It seems that I am running Excel 8 or perhaps 10, on Microsoft Edge.

    I would appreciate any suggestions on how to achieve this.

    I do have the template and my first sheet’s current table. This table will require updates/refreshing as it revolves/changes monthly. I can send the current one and template if you can let me know where to send it to for clarity.

    Thanking you for your assistance.

    Erika Cartmell

    Reply

  • Back in 2016 you had an article showing how one could copy data from one sheet to another based on criteria. I tried to use your formula; but, could not get it to work for my spreadsheet?

    Can you please help me with the attached spreadsheet so it will copy the row to its corresponding team member name tab if Column C shows “Y” and Team Member Response Column H either shows “All” or matches Team Member Name in cell B5.

    I sent the spreadsheet to your email address as I do not currently have access to a SharePoint drive.

    Thank you in advance for your assistance.

    Kelly

    Reply

    • Hi,

      Please get rid of the merged and centred cells from the first worksheet. In cell A2 of the second worksheet, enter this formula

      =FILTER(‘Level 2 – Coverage Review’!$A$15:$I$652,((‘Level 2 – Coverage Review’!$C$15:$C$652=”Y”)*(‘Level 2 – Coverage Review’!$H$15:$H$652=’Level 2 – Coverage Review’!$B$5)))

      Hope this helps.

      Reply