I am trying to create a macro to auto populate an invoice template (excel) from data held within an excel spreadsheet, but I also want to specify the month for invoicing. e.g. If January is selected in a cell it takes only invoices that appear in January, then adds each row to the invoice for January
I have a basic understanding of Macros but wondered if anyone can help with this?
Happy to send the spreadsheets via private message.
Very new to VBA, would appreciate any help/feedback!
Hi Cantona166
Welcome to the Forum.
I am certain that we can assist. Please uipload a sample file that we can make use of for testing.
To attach a file, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
Keep everything here in this thread, please.Originally Posted by Cantona166
Rule 4 of our Forum RULES: Don't Private Message, Visitor message or email Excel questions to moderators or other members. (Or Access, Word, etc.)
All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.
Post your question in a public thread and our many contributors will come to you to assist, especially if the title is accurate (see Rule #1) and you include a sample desensitized workbook that makes it easy for others to try and help.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Great thanks!
Basically I need the excel spreadsheet data to populate the areas highlighted in yellow on the invoice sheet. If it is possible I would like it to only paste the invoice details based on the month selected in
cell F73.
For example if August is selected the following project ref will be ready for invoice:-
RWA-17-18-01
RWA-17-18-01
RWA-17-18-12
RWA-17-18-13
RWA-17-18-05
RWA-17-18-11
RWA-17-18-13
But I would like it to be clever enough to post individual invoices for each project ref, if there are two it will past two line (RWA-17-18-01)
Hope that makes sense?
Do you want the invoices saved to a invoice folder in PDF format?
That would be good if possible
Have a look at attached. I incorporated the invoice template into same workbook and moved the date selection to [D2]
If there is duplicate project ref numbers i just added the totals together on one invoice and displayed both invoice no's in the invoice number cell.
Saves invoices to the path that you stored this workbook....
Please Login or Register to view this content.
Thanks very much for this, but I can't seem to get it to work?
What can't you get it to do...select the month in D2 and run the macro...
Thanks it worked fine but is it possible for it to generate a separate invoice for each project ref in each month e.g. for August, RWA-17-18-01, RWA-17-18-012, RWA-17-18-013 & RWA-17-18-05? if there are two refs then it would return both lines. At the moment it is only returning the one for RWA-17-18-05.
really appreciate your help
So you want me to list both projects on same invoice with individual totals...What about the different invoice no's as per column HIf there is duplicate project ref numbers i just added the totals together on one invoice and displayed both invoice no's in the invoice number cell.
What do you mean
That is a single entry only for August. RWA-17-18-01 is a duplicate for AugustAt the moment it is only returning the one for RWA-17-18-05.
Edit...Herewith amended code...
Please Login or Register to view this content.
Last edited by sintek; 12-28-2017 at 09:29 AM. Reason: added red part of code
Yes I require duplicate project no's to be on separate lines, the invoice numbers currently on the sheet are just dummies, in real life they will have the same invoice no if both invoiced in the same month.
What I mean is at the moment when I run the macro it only returns one invoice for august for ref RWA-17-18-05, when it should return 4 separate invoices, one for RWA-17-18-01 (with two entries), one for RWA-17-18-05, one for RWA-17-18-12 and one for RWA-17-18-13
Change this line
toPlease Login or Register to view this content.
and it will render 4 invoicesPlease Login or Register to view this content.
Edit
Add this partjust belowPlease Login or Register to view this content.Please Login or Register to view this content.
Last edited by sintek; 12-28-2017 at 09:30 AM.
Thats great! thanks for all your help!
Pleasure...Tx for rep +
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Hi Sintek
I have just copied your macro into my live spreadsheet and I keep getting a bug on the .AutoFilter 5,Month line. Am I correct in thinking this relates to column 5 of the spreadsheet and is where it selects the month?
Not sure why its giving me an error?
Yes, Column 5...What error is it throwing. Works on upload.
The error is:-
Run-time error '1004':
Autofilter method of range class failed
Post the code you are using or a sample of the current scenario...must have changes since last upload?
I have attached the spreadsheet I am now using with the following code:-
Please Login or Register to view this content.
Last edited by Cantona166; 12-30-2017 at 08:18 AM.
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
Sorry very new to this forum, hope its ok now
Yes - thanks.
Please Login or Register to view this content.
@ sintek
Just fyi, no need to add ".pdf" in filename. It's added automatically when selecting type in ExportAsFixedFormat.
Please Login or Register to view this content.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
Perfect!
Thank you
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Glad you got it sorted...
@ bakerman2 Noted, tx
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks