+ Reply to Thread
Results 1 to 20 of 20

Create new file based on first 100 Rows

  1. #1
    Forum Contributor
    Join Date
    01-26-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2013
    Posts
    154

    Create new file based on first 100 Rows

    Dear Experts ,

    This may be a bit of challenge , but going by the help that i have received i am hopeful that someone can pick this up !

    What i have is a excel file containing thousands of rows of question answers with the first row containing titlles

    What i want the macro to do is keep the titles in row 1 constant and create a separate excel workbook for every 100 rows and store it in the same location as the parent workbook
    Say i have a folder on the desktop called XYZ containing this input workbook say test 1 containing more than 1000 entries
    what i would be wanting the macro to do is then split a 1000 Row workbook into workbooks containing 100 rows each and save it in the same folder ...they can be named run 1 , run 2 ,run3 etc

    In each of this run books , i want the header row containing the titles same as in the parent workbook and then the rows to appear
    After the 100 rows in each workbook , i want "END " to appear in cell A1

    it is quite possible that some sheets may have 1000 , some 30000 or some 456 entries
    So , if the progarmming can be done such that it counts the total number of rows and then splits in equal 100 numbers and what remains as balance in the last trial workbook , it would be nice
    For example if there are 356 rows , we can have trial 1 ,2,3 containing 100 rows each ( excluding the title and end in A1 ) and trial 4 containing 56 rows

    Then each trial 1 ,2,3,4,getting saved in the same folder where the 356 row file was originally there would be great ,

    I attach a sample input and output file of how things would be simulated !
    It is a bit tough , i know and i would be glad if someone can attempt it !

    The parent folder with the file eager to be executed shall be on the desktop

    Humble regards ,

    Amlan Dutta
    Attached Files Attached Files

  2. #2
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Create new file based on first 100 Rows

    Hi Amlan

    Point of clarification...your Sample File (INPUT100BREAK) appears to have records that end at Row 202 which would indicate 201 records after eliminating the Header Row.

    However, Row 1002 has the word "END" in it. So, how many records are we dealing with in the Sample File, 201 or 1001?

    Edit: Problem here...

    This appears to be a Duplicate Thread...http://www.excelforum.com/excel-prog...me-folder.html
    Last edited by jaslake; 09-25-2014 at 04:08 PM.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Forum Contributor
    Join Date
    01-26-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2013
    Posts
    154

    Red face Re: Create new file based on first 100 Rows

    Dear Jaslake ,

    It's a pleasure seeing you again mate .Thanks your interest in this one ...sorry for the mistake in the earlier Input file
    i have redone the file and attached it again in a much precise format .Please see attached file "input"

    Sir , there are basically two things that i intend the macro to do here , namely

    Part (1) - SEPARATING WORKBOOK INTO SEPARATE TABS OF 100 ENTRIES EACH

    You can see in the attached "input" workbook , the "input" tab in the given case contains 200 entries excluding the header row and one after the last 200th entry containing "END"in cell A1

    What i was desiring here is the macro to separate out entries in multiples of 100 from the parent and form separate tabs in the same workbook for each such 100 entries . since this tabs would contain separate entries , it would be lovely if they can be named based on the rows that they fetch i.e input1-100 , input101-200 and so on ---i prefix input because the parent tab here is named input


    Here , in given case there can be only 2 tabs possible because there are 200 entries and since we split in terms of 100 , so after the 2nd tab , the macro has no more results to fetch
    However in actual run utility , there can be entries like 556 original tab where the macro will make five hundred tabs and then the tab containing 56 rows ...something like input1-100,input101-200,input201-300,input301-400,input401-500,input501-556 each having the header row and the row after the last row containing"END in Cell A1 "

    (2) Part -2 - Separate workbook based on tabs

    At this phase, after the first activity is complete , i intended to have separate workbook for each such tab based on the tab name and in the same location as the source folder

    so in the cited case say the source file is in a folder test in the desktop , once the macro is run and the tabs input 1-100 , input 101-200 formed , they will separate out themselves and form separate workbooks based on the tab name input 0 -100 , input 101 -200 etc and so on in the same test source folder,


    What you observe as duplicate entry is actually part 2 of the project .
    When noone could take this up , i manually did Part 1 and thought that even if someone can automate Part 2 of forming separate workbooks , that will save me sometime ...so that was actually not a replicate but a part of this project

    That's all , sir ,

    And thanks a toone for replying on this done , i remember how you helped in many of my past cases ..


    Cya ,

    Amlan
    Attached Files Attached Files

  4. #4
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,121

    Re: Create new file based on first 100 Rows

    Here's my effort.

    I created a index file to launch the macro from. This is so you can break up multiple question files without having to copy the code from file to file.
    Attached Files Attached Files
    David
    (*) Reputation points appreciated.

  5. #5
    Forum Contributor
    Join Date
    01-26-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2013
    Posts
    154

    Re: Create new file based on first 100 Rows

    Tinbendr , this is awesome , mate ...it works like a dream ...i could not have asked for more
    You have exceeded all my expectations that i could have had ....i wished i had not manually done the first part now

    This macro works like a dream and infact you went one step further and hard coded the row splitter so that i can use the userform that you have so intutively built so that i can divide in as many rows which need not be always 100 ...so i tested for 50 and it still works like a dream ...i have tested it on 3 test files containing more than 1000 rows with 24 variations and it still works .....

    I would have just liked the fact that the tab remains as well in the parent sheet before getting separated
    I mean , what happens now is that the tabs neatly separate out (which is what i want ) but while doing so they come out from the parent workbook
    Can they remain in the parent workbook as independent tabs and then also form separate workbooks of their own (which is already happening )

    Also i want to turn on screenupdating to see all this ...can you lemme know where i need to set screenupdating to true so that i can watch all this happen ,

    I will mark the thread as solved since all this are just my desire and not something that goes against the miracle that you have already made
    i am also assingning all the reputation points that you richly deserve !

    Thanks so so so much ...it means so so so so much to me , this macro and i am in love with how i can use it to operate on any file without having to add the VB code to it ...this is miraculous else i would be needing to add VB code to every sheet and that would be a huge pain in the bump !

    Thanks again ,

    Peace ,

    Amlan Dutta

  6. #6
    Forum Contributor
    Join Date
    01-26-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2013
    Posts
    154

    Re: Create new file based on first 100 Rows

    Sir , i just set the screenupdating to true and i can now see the thing as it happens !

    For purpose of my project , i needed the workbooks generated in excel Excel 97 2003 format
    (When we save the file , we get option to save as many format's..of the format type , 97 2003 is the one which PHP excel library recognises for synchronisation to PHPmyadmin !

    Is there a way using which i can use your code to simulate the same results but have the ouput workbooks in 2003 format rather than in 2007 format !

    I am sorry for this additional request !I am also trying to see if i can understand and do the change myself for changing the output format but it is proving difficult for me ,

    Peace ,

    Amlan Dutta

  7. #7
    Forum Contributor
    Join Date
    01-26-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2013
    Posts
    154

    Re: Create new file based on first 100 Rows

    Sir, actually , i did manage to spot the code in the userform where simply changing the format to xls does give me workbooks in the intended excel 1997 2003 format ...but for some reason this doesn't work with the other application ...however if i save the same workbook as 1997 2003 format, then the manually saved file in 97 2003 format works ...is it that application file saved in 1997 2003 doesn't inherently change it's attribute !

    I did this in the code prepared by you ,

    WB2.SaveAs Me.txtPrefix & ShtCnt & ".xls", 51




    Peace ,

    Amlan Dutta

  8. #8
    Forum Contributor
    Join Date
    01-26-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2013
    Posts
    154

    Re: Create new file based on first 100 Rows

    Sir , the file extension issue has been sorted out ,so please don't bother anymore ...i have managed the other application to read the excel 2007 files with a reader library ...and now all the tabs that get generated using your macro work just fine for my project (In fact it works like a dream and i can't be more grateful for this marvelous piece of code)

    All i can just wish now is that the worksheets (tabs) remain in the parent file besides being separated ......

    That would be really nice for my project ,

    All other things that i wanted to have (screenupdating and change in extension of the file , i managed to tweak your code a bit and get around it ...
    Just this part of having the tabs appear in parent workbook as well is bit tough for me ,

    Thanks a tonne in advance ...

    Humble regard's,

    Amlan Dutta
    Last edited by amlan009; 09-26-2014 at 10:41 PM. Reason: typographical errors and grammar mistakes

  9. #9
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,121

    Re: Create new file based on first 100 Rows

    Sorry, I got tied up at work.

    So you want a separate worksheet inside the source workbook for each division AS WELL as a separate file?

    I'm pleased you like it so well.

    David

  10. #10
    Forum Contributor
    Join Date
    01-26-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2013
    Posts
    154

    Re: Create new file based on first 100 Rows

    yes,sir ...the separate files are already made available with the code

    I would love this this separate worksheets remain in the original workbook as well

    Let me illustrate ....say i have a "test" workbook with 200 rows and i split it in 100 rows and set prefix "run" , what i get at present is "run1" and "run2" workbooks each containing 100 rows
    What i would love is (1) having this "run1" and "run2" as worksheets in the same "test" workbook as well as (2) them getting separated
    Since they already separate , so just the (1) would be awesome to have

    Thank's

    And you don't need to be sorry ...the macro as it is does everything that i need ..in fact it does so much more ....

    Peace ,

    Amlan Dutta

  11. #11
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,121

    Re: Create new file based on first 100 Rows

    I think this will do it. I tested it a couple of times and it seems to work.
    Attached Files Attached Files
    Last edited by Tinbendr; 09-30-2014 at 09:31 PM.

  12. #12
    Forum Contributor
    Join Date
    01-26-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2013
    Posts
    154

    Re: Create new file based on first 100 Rows

    Thanks a tonne , david ....this again works like a dream just like the earlier one !
    It's a tooo good macro and a amazing bit of code which really helps my project ,

    I don't know how to thank you for this ......this means a lot lot to me ,

    It works on all the 3 test files i tested with more than 30000 rows with various different row counters ,

    Humble regards ,

    Amlan Dutta

  13. #13
    Forum Contributor
    Join Date
    01-26-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2013
    Posts
    154

    Re: Create new file based on first 100 Rows

    Dear David ,

    Thanks a tonne for help on the other posts as well ....i have benefited a lot from your help on this project as well ....in this project , as you know , we have now the option of splitting into tabs and also separate workbooks !
    In the tabs that get generated as also the workbooks , the column width changes from that of the parent sheet. Is it possible that the formatting of the parent sheet gets reflected in the tabs and the workbooks ....

    Also , presently , it gives us both , which is exactly what i wanted
    But at times , the requirement is to have just one ---either tab or workbook ...can we have the userform define this part and based on
    userinput execute the output ...for example , a checkbox for tabs and another checkbox for workbooks would be nice!
    If user checks tabs , then tabs would be generated , if user checks workbooks , workbooks would be generated !
    If user checks both , then both tabs and workbooks would be generated !

    I am sorry if this sounds complex and i am absolutely fine with the existing work as it is as well !
    Thanks a tonne for all help you have provided me ..i am sure that not many know of this userform concept where sheets can be imported so that code need not be copied from sheet to sheet !
    Thanks much ,

    Humble regards ,

    Amlan Dutta

  14. #14
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,121

    Re: Create new file based on first 100 Rows

    OK, see if we have a 'meeting of the minds'.

    This gives the option of new Workbook and/or worksheets in source WB.

    If also makes the columns the same with as the source.
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    01-26-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2013
    Posts
    154

    Re: Create new file based on first 100 Rows

    Dear David ,

    I am so happy that experts like you are real ..it's like fantasy land where we can ask for help and then there is some genie doing magic ....it is all so wonderful...i attach your code with output in my attachment ....

    (1) let me tell you that the formatting is perfect in output sheets...the column widths are maintained
    (2) Also as requested, The macro does the split of workbook and tab exactly as i wanted

    So all that i want is done !

    There was a small issue that i spotted in the first 2 tabs that get created and all the other tabs are perfect , you will see in the attached file though it was supposed to pick up 10 rows from the parent tab , it picked up 3 ...so if you look at Skill 001-010 and skill 011-020 (I used Skill as a prefix because it's a question bank ), you will see that there are 3 entries !
    But in all other tabs Skill 021-30 , etc and the other 230 tabs , the rows are 10 in number !
    So, except that minor hitch, rest works like a dream !

    Nevertheless , what the macro still does is something that blows my mind away .....You are the bestest ever ever !
    Thanks so so so much.....hug's from my entire team ...you really rock , man ...all this would take me so much time to do manually !

    Cheers ,

    Amlan Dutta
    Attached Files Attached Files

  16. #16
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,121

    Re: Create new file based on first 100 Rows

    I d/l your file and it works fine.

  17. #17
    Forum Contributor
    Join Date
    01-26-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2013
    Posts
    154

    Re: Create new file based on first 100 Rows

    I am sorry , i downloaded it myself and reran ...it's now showing correct results ...must be some thing to do with my system...but am really sorry for the hassle ....
    It's all like a dream and everything works just like i want it to ...must have taken you lot of efforts for this ...am really sorry to make you go through all the pain to develop this magic code !
    Thanks so so so much !

  18. #18
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,121

    Re: Create new file based on first 100 Rows

    I glad it worked out and glad you like it so well.

  19. #19
    Forum Contributor
    Join Date
    01-26-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2013
    Posts
    154

    Re: Create new file based on first 100 Rows

    Thanks much

  20. #20
    Forum Contributor
    Join Date
    01-26-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2013
    Posts
    154

    Re: Create new file based on first 100 Rows

    Also wish you a very sweet merry christmas! You made my christmas so special this year ! Thank you so so so so muchhhhhhhhhhhhhh!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Create save as new file for each row by copying all rows from another excell file
    By snsuvarna in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-16-2013, 09:17 AM
  2. Need to Create a Catalog based off an Excel File
    By tlb4327 in forum Excel General
    Replies: 5
    Last Post: 07-16-2013, 09:36 PM
  3. Create pdf file name based on value of certain cells
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-24-2012, 03:20 PM
  4. Create new Excel file based on category
    By Jcolby in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-19-2009, 01:28 PM
  5. How to create file in Excel with data in rows
    By toplisek in forum Excel General
    Replies: 1
    Last Post: 06-17-2008, 12:14 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1