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
Last edited by elgato74; 11-15-2014 at 08:18 AM.
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:
Ciao,
Please Login or Register to view this content.
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
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 !
Hi, Felix,
and if you really want an invoice sheet for each invoice have a look at
Ciao,Please Login or Register to view this content.
Holger
double post
Last edited by elgato74; 11-15-2014 at 09:30 AM.
Error 1004 Cannot rename a sheet to the same name
Hi, Felix,
stupid mistake - if you copy the same sheet you should augment the number on that sheet or store it in another variable.
Ciao,Please Login or Register to view this content.
Holger
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 ...
Hi, Felix,
take away the custom formatting of H19 in Sheet "1" and alter the code like
Again: my mistake to forget about the additional Rechnungsnummern to be cared for in the code.Please Login or Register to view this content.
Ciao,
Holger
one minor little bug, the first two sheets are then identical ...
Hi, Felix,
Sheet "1" being a template, which sheets are you talking about?
Ciao,
Holger
yes, sheet 1 being a template, but already being numbered ... so my actual first invoice has Rechnungsnummer "2"
first actual invoice should have Rechnungsnummer "1"
Hi, Felix,
have you thought about entering 0 into the template?
Ciao,
Holger
Duh ! now i have)
... but when i change it to "0" - it gives me the error code again about renaming the same sheet ...
Hi, Felix,
what would be the sheetname for Rechnungsnummer 0? Definitely not 1Change the worksheet tab as well as the only position in the code from "1" to "Vorlage" (worksheet tab without quotes, please).
Ciao,
Holger
So I changed the tabname on my template sheet to Vorlage and altered the code
now i get an "out of range" error ... i am just no coderPlease Login or Register to view this content.
I got it !! There was another location in the code that had to be changed to "Vorlage"
Getting there
Hi, Felix,
there are two instances where the name appeared, the other is in the Copy-command.
Ciao,Please Login or Register to view this content.
Holger
now it's perfect ! thank you very much indeed Holger. Hope all is well with you and have a splendid weekend !!
Felix
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks