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


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.

RSS 19 Comments…

 Share your views
  1. 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


    • Hi,

      You are welcome. What do you mean by "i just want to create a reports for all vendors(no restrictions)"? Please clarify

      • 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

        • 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.

          • 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

  2. 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??

  3. thank you so much sir!.. you are indeed genius.

  4. 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)


  5. thanks a lot sir!.. i learnt a lot from you.

  6. 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

    • 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.

  7. Dear Ashish

    How can i create typing list box? And how can i change list box data.

  8. Dear Ashish

    I want to use your file on report budget and actual.

    How can i copy column of Description 2016 Actual AP 2016 datas

    And can i summarize datas using filter for example: I want to summarize using classification names. And please check run time error 1004 . I can't fix this error

    Sorry for my bad english

    I attached my file https://www.dropbox.com/s/9u0zmtnoxuk6nrn/Test10-%20Copy.xlsm?dl=0

Leave a Comment

Your email address will not be published.