+ Reply to Thread
Results 1 to 21 of 21

Macro to create new sheets from rows

  1. #1
    Forum Contributor
    Join Date
    12-05-2008
    Location
    Germany
    MS-Off Ver
    365
    Posts
    432

    Macro to create new sheets from rows

    Dear All,

    please have a look at the attached file. The task is to create invoices from each row in the first sheet.

    - Name always goes into C10
    - Adresse always goes into C11
    - PLZ always in C12
    - Ort always in D12
    - Land always C13
    - Datum always H18
    - Brutto always F27

    An additional task is to fill in the field "Rechnungsnummer" - it should count one up on each sheet.

    All other fields are static, resp. will be calculated automatically through simple formulas.

    Thanks in advance !!!

    Felix
    Attached Files Attached Files
    Last edited by elgato74; 11-15-2014 at 08:18 AM.

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,097

    Re: Macro to create new sheets from rows

    Hi, Felix,

    I think a lot of members in this Forum will have a problems understanding the names of your Variables.

    Give this code a try. It adds Rechnungsnummer on Sheet Herunterladen in Column AC and will avoid to print the invoices a couple of times if the number is filled.

    I used PrintPreview here, you would need to comment the command out and use PritnOut and maybe specify the number of copies you want printed:
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Forum Contributor
    Join Date
    12-05-2008
    Location
    Germany
    MS-Off Ver
    365
    Posts
    432

    Re: Macro to create new sheets from rows

    Hi Holger,

    I guess I was not clear enough on the requirement. I need for every row on Sheet "Herunterladen" a new sheet that resembles the invoice for the row. So after running the macro, the workbook will have 185 sheets with invoices which I can then print. No need for the preview option...

    Thanks a bunch !

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,097

    Re: Macro to create new sheets from rows

    Hi, Felix,

    and if you really want an invoice sheet for each invoice have a look at
    Please Login or Register  to view this content.
    Ciao,
    Holger

  5. #5
    Forum Contributor
    Join Date
    12-05-2008
    Location
    Germany
    MS-Off Ver
    365
    Posts
    432

    Re: Macro to create new sheets from rows

    double post
    Last edited by elgato74; 11-15-2014 at 09:30 AM.

  6. #6
    Forum Contributor
    Join Date
    12-05-2008
    Location
    Germany
    MS-Off Ver
    365
    Posts
    432

    Re: Macro to create new sheets from rows

    Error 1004 Cannot rename a sheet to the same name

  7. #7
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,097

    Re: Macro to create new sheets from rows

    Hi, Felix,

    stupid mistake - if you copy the same sheet you should augment the number on that sheet or store it in another variable.

    Please Login or Register  to view this content.
    Ciao,
    Holger

  8. #8
    Forum Contributor
    Join Date
    12-05-2008
    Location
    Germany
    MS-Off Ver
    365
    Posts
    432

    Re: Macro to create new sheets from rows

    Awesome Holger ! Is there any way to keep the original format of cell H19 "Rechnungsnummer" ? Rather than 1,2,3,4... I would need something like 2013-001, 2013-002, and so on ...

  9. #9
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,097

    Re: Macro to create new sheets from rows

    Hi, Felix,

    take away the custom formatting of H19 in Sheet "1" and alter the code like
    Please Login or Register  to view this content.
    Again: my mistake to forget about the additional Rechnungsnummern to be cared for in the code.

    Ciao,
    Holger

  10. #10
    Forum Contributor
    Join Date
    12-05-2008
    Location
    Germany
    MS-Off Ver
    365
    Posts
    432

    Re: Macro to create new sheets from rows

    one minor little bug, the first two sheets are then identical ...

  11. #11
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,097

    Re: Macro to create new sheets from rows

    Hi, Felix,

    Sheet "1" being a template, which sheets are you talking about?

    Ciao,
    Holger

  12. #12
    Forum Contributor
    Join Date
    12-05-2008
    Location
    Germany
    MS-Off Ver
    365
    Posts
    432

    Re: Macro to create new sheets from rows

    yes, sheet 1 being a template, but already being numbered ... so my actual first invoice has Rechnungsnummer "2"

  13. #13
    Forum Contributor
    Join Date
    12-05-2008
    Location
    Germany
    MS-Off Ver
    365
    Posts
    432

    Re: Macro to create new sheets from rows

    first actual invoice should have Rechnungsnummer "1"

  14. #14
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,097

    Re: Macro to create new sheets from rows

    Hi, Felix,

    have you thought about entering 0 into the template?

    Ciao,
    Holger

  15. #15
    Forum Contributor
    Join Date
    12-05-2008
    Location
    Germany
    MS-Off Ver
    365
    Posts
    432

    Re: Macro to create new sheets from rows

    Duh ! now i have )

  16. #16
    Forum Contributor
    Join Date
    12-05-2008
    Location
    Germany
    MS-Off Ver
    365
    Posts
    432

    Re: Macro to create new sheets from rows

    ... but when i change it to "0" - it gives me the error code again about renaming the same sheet ...

  17. #17
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,097

    Re: Macro to create new sheets from rows

    Hi, Felix,

    what would be the sheetname for Rechnungsnummer 0? Definitely not 1 Change the worksheet tab as well as the only position in the code from "1" to "Vorlage" (worksheet tab without quotes, please).

    Ciao,
    Holger

  18. #18
    Forum Contributor
    Join Date
    12-05-2008
    Location
    Germany
    MS-Off Ver
    365
    Posts
    432

    Re: Macro to create new sheets from rows

    So I changed the tabname on my template sheet to Vorlage and altered the code

    Please Login or Register  to view this content.
    now i get an "out of range" error ... i am just no coder

  19. #19
    Forum Contributor
    Join Date
    12-05-2008
    Location
    Germany
    MS-Off Ver
    365
    Posts
    432

    Re: Macro to create new sheets from rows

    I got it !! There was another location in the code that had to be changed to "Vorlage"

    Getting there

  20. #20
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,097

    Re: Macro to create new sheets from rows

    Hi, Felix,

    there are two instances where the name appeared, the other is in the Copy-command.

    Please Login or Register  to view this content.
    Ciao,
    Holger

  21. #21
    Forum Contributor
    Join Date
    12-05-2008
    Location
    Germany
    MS-Off Ver
    365
    Posts
    432

    Re: Macro to create new sheets from rows

    now it's perfect ! thank you very much indeed Holger. Hope all is well with you and have a splendid weekend !!

    Felix

+ 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. [SOLVED] Rows to Sheets - Auto Create - Please help
    By elite-fusion in forum Excel Programming / VBA / Macros
    Replies: 46
    Last Post: 11-15-2013, 12:44 PM
  2. Replies: 2
    Last Post: 09-19-2013, 04:22 AM
  3. Copy Rows By Date and create new sheets
    By Bpd in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-05-2013, 05:54 PM
  4. Create very sheets with select rows/columns from master data file
    By oscarsonthepond in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-22-2012, 01:33 PM
  5. [SOLVED] create sheets and copy rows
    By Newbie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2006, 10:10 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