+ Reply to Thread
Results 1 to 47 of 47

Rows to Sheets - Auto Create - Please help

  1. #1
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Rows to Sheets - Auto Create - Please help

    Hey Guru's,

    I have a question for everyone who knows excel a lot better than me, which mostly all of you haha.

    I want to create an excel spreadsheet which will act like so:

    Sheet 1 has a list of all the applications that I have on my computer:
    Please Login or Register  to view this content.
    For each row, I would like to have a new sheet automatically created. So for example. I have 5 rows, should have a total of 6 sheets (6th is the first main one Sheet 1).

    Also, on Sheet 1, I will have not only a list of all the applications but also information about it, like so:
    Please Login or Register  to view this content.
    For each row, I would like to have a new sheet automatically created, but also have the fields filled in according to a template that I want to design.

    Pretty much I need Sheet 1 to be the main source of information, sort of a one stop shop to add/edit any information, and for the rest of the sheets to feed/update information from Sheet 1.

    Can someone please advise if this is doable?

  2. #2
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    Yes its doable. So you want that as soon as you enter the data into Sheet1, it should be updated into the new sheet and if the sheet already exists then to the next available row?

    Would you want the data to be copied instantly or would you be updating all the info and then click a button to trigger the copy?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    I think I would prefer to do it automatically, if possible.

    Please advise

  4. #4
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    Should Sheet1 be cleared once the info is copied?

  5. #5
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    Nope, I want sheet one to always retain its information and if I need to edit an application which is on Sheet92 for example, I would just go to Row 92 (or 93 depending if I have a title row) on Sheet1 and edit the info from there.

  6. #6
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    And if you edit row 93, you want the corresponding entry in the specified sheet to be edited as well?

  7. #7
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    Yes. If it would be easier to do it via press of a button instead of instantly, that will work just as well. because I would like for the sheet to be named with the application name in Column A of Sheet 1. or is that too much to ask? Not sure if it can even be done

  8. #8
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    Yes, it can be done. The best way to do this would be - each time you make an update to the master sheet (sheet1), click on the button which will clear up / delete the other sheets and re-populate from scratch. This way, there is no double entry in the other sheets and no updates to the master data are missed out.

  9. #9
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    That is perfectly fine with me Now...how do I get this done haha?

    By the way, thank you very much for your help so far

  10. #10
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    Any help?

  11. #11
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    Try this code
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose View | Macros
    Select a macro in the list, and click the Run button

  12. #12
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    Great. I just tried it, and it did work.
    I do have another question though, is there anyway to make it so that it pre-formats each tab, lets say I want to have a template where on each new sheet the name of the application is in the first row and is bold and size 16, then the description will have the word "Description" on one row, and right under will have the actual description from the Master sheet?

  13. #13
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    Yes its possible. If its a small formatting to be done, then we can do it through code. However, if its more intricate involving color formatting, borders, etc, then its ideal to have a template tab (maybe hidden) in the file which can be copied each time a sheet needs to be created.

  14. #14
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    It would probably be a bit more intricate.
    so I can create a template in a sheet and hide it, but then I am assuming the initial code will change from that one that you gave me to paste as a macro, so how would I do that?

  15. #15
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    Upload the file and i can help you with it.

  16. #16
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    Here you go.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    Can you specify what details should go where? Have you already done the font formatting, colors, etc to the template?

  18. #18
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    Yep, The template can be that way, it is fine. If anything I will try to change it at a later time, but for the time being, that template is alright.

  19. #19
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    You had mentioned earlier that once the template is copied, you wanted certain details to be entered into it. So where should the entries go?

  20. #20
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    Master A column is application name (row 1 in template sheet), B column is version (row 7 in template sheet) C column is description (row 4 in template sheet), D is 10, E is 13, F is 32.

  21. #21
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    So, any luck with this ?

  22. #22
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    You need to wait before bumping your thread. All of us are here voluntarily and may not be online at all times.

    I will provide you a solution in sometime.

  23. #23
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    Sorry about that. I am just trying to get this done soon. Please let me know once you have something Thank you for your help.

  24. #24
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    Try this updated code -
    Please Login or Register  to view this content.

  25. #25
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    This worked great, but still not exactly the way I want it. I tweaked the template so that it generates the information better, but still it is missing the headers of each field, Description, Version, Last Revised, etc...

    Please advise. I have attached an updated copy and also included a SAMPLE sheet how I would love for it to look like.
    Attached Files Attached Files

  26. #26
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    The headers need to go in the template, so that the code doesnt have to put them in. They will be automatically present in each copied version of the template.

  27. #27
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    But they are not. I just ran the macro again and still, same thing. Only information from the sheet is populated, not the information of the headers from the template.

  28. #28
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    Any update on this? Please help

  29. #29
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    Sorry, i think you are not getting what i am saying. You need to enter the headers in the template and that is just a one-time activity.

    Each time the template sheet is copied, the headers will go through automatically. The macro will then populate only the values from the master file.

  30. #30
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    Yes, I do understand what you mean. The heads ARE in the template. Please see the file that I have attached on Friday. You can see that the headers are in the template, but they do not get copied, and once the Macro is run, it removes the headers from the Template spreadsheet.

    Please advise.

  31. #31
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    Am sorry, i got it.

    Earlier, the macro was clearing contents of all sheets except the master. Now that the template has come in, you need to change this code from
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  32. #32
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    For some reason I do not see the last post in this thread, but luckily I was able to see it in the email notification.
    Just wanted to say that it works great. I have already modified the template and also changed the code a bit in order to reflect the change.

    Thank you very much.

    I was just wondering if you might know of a quick macro command in order to delete all sheets with the exception of the Template and Master sheets? Because looks like I need to delete all other sheets in order for the template change to take affect.

    I thought that it was supposed to originally work so that it removes all the sheets and re-creates them, not just updates the current sheets.

    I do not mind, but would be great to have a command to remove all sheets with the exception of the first two (Template and Master).

    Please advise

  33. #33
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    Replace this code
    Please Login or Register  to view this content.
    with
    Please Login or Register  to view this content.

  34. #34
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    Great. Works perfectly, but still with the exception of one tiny thing.

    In the Template sheet, I have B1 and C1 merged into one cell. But when the template gets copied, the B1 and C1 are two separate cells and it causes the name of the application to not be centered on the page, but instead only centered on B1.

    Any ideas?

  35. #35
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    In the last file you attached, there is data only in column B. Nothing in column C. I just did a test run at my end and i also found that the merge doesnt stay. We will need to merge them ourselves.

    Also, you will be getting the popup to press Delete each time the sheet has to be deleted, so you can add these lines -

    After
    Please Login or Register  to view this content.
    add
    Please Login or Register  to view this content.
    At the end of your code after
    Please Login or Register  to view this content.
    add
    Please Login or Register  to view this content.

  36. #36
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    To merge the cells, just add a line of code -

    Below
    Please Login or Register  to view this content.
    add
    Please Login or Register  to view this content.

  37. #37
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    YOU ARE THE BEST!
    Thank you. Now this is the way I want it to be. I wish I knew this stuff as well as you

  38. #38
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    Am glad its solved and thanks for the rep.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. (I will do it for you this time, this is for future reference).

  39. #39
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    Thank you I forgot to do that yesterday. As for Reputation, that I added to you yesterday

  40. #40
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    Hello again,

    I was wondering if you might be able to assist again. I am looking to create a macro that will remove all the sheets except Template and Master, the only reason for that is so that I can run it when I need to update the Master sheet so it doesn't clutter up the workspace.

    Please advise on how I can do that.

  41. #41
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    You want me to edit the macro in this thread to add this part?

  42. #42
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    Can there be two different commands in one macro? I just want to be able to run a macro so it removes all the sheets except the Template and Master sheets, and then another macro that I can run that will re-populate the original macro that makes the sheets.

  43. #43
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    You can have 2 separate macros but you cannot have 2 commands in one macro.

    Post 31 is already containing the changes to be done to delete the sheets. Do you want to run it separately and not as part of the macro i gave you?

  44. #44
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    Yes please.

  45. #45
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    Try this

    Please Login or Register  to view this content.

  46. #46
    Registered User
    Join Date
    09-02-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Rows to Sheets - Auto Create - Please help

    Great. Thank you very much. I also added the code to not show notifications for me to click on OK 20 times.

  47. #47
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Rows to Sheets - Auto Create - Please help

    Oops sorry i forgot about that. I just manually typed the code, so missed it out.

    Glad its solved.

+ 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. Auto create and delete rows
    By Republic.Of.Gamers in forum Excel General
    Replies: 0
    Last Post: 09-12-2013, 02:30 AM
  2. Replies: 10
    Last Post: 02-14-2011, 12:51 PM
  3. Solution to auto create labels for chosen rows?
    By comptechbranden in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-30-2007, 11:23 AM
  4. how do I create a macro to auto insert rows?
    By aashish in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-30-2006, 07:55 PM
  5. Auto create sheets and web query using VBA
    By claytorm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-09-2005, 03:55 AM

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