+ Reply to Thread
Results 1 to 29 of 29

Macro to auto populate template from excel spreadsheet

  1. #1
    Registered User
    Join Date
    12-28-2017
    Location
    London
    MS-Off Ver
    2011
    Posts
    12

    Macro to auto populate template from excel spreadsheet

    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!

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,672

    Re: Macro to auto populate template from excel spreadsheet

    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!!!

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,342

    Re: Macro to auto populate template from excel spreadsheet

    Quote Originally Posted by Cantona166 View Post
    Happy to send the spreadsheets via private message.
    Keep everything here in this thread, please.

    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" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  4. #4
    Registered User
    Join Date
    12-28-2017
    Location
    London
    MS-Off Ver
    2011
    Posts
    12

    Re: Macro to auto populate template from excel spreadsheet

    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?
    Attached Files Attached Files

  5. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,672

    Re: Macro to auto populate template from excel spreadsheet

    Do you want the invoices saved to a invoice folder in PDF format?

  6. #6
    Registered User
    Join Date
    12-28-2017
    Location
    London
    MS-Off Ver
    2011
    Posts
    12

    Re: Macro to auto populate template from excel spreadsheet

    That would be good if possible

  7. #7
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,672

    Re: Macro to auto populate template from excel spreadsheet

    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.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-28-2017
    Location
    London
    MS-Off Ver
    2011
    Posts
    12

    Re: Macro to auto populate template from excel spreadsheet

    Thanks very much for this, but I can't seem to get it to work?

  9. #9
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,672

    Re: Macro to auto populate template from excel spreadsheet

    What can't you get it to do...select the month in D2 and run the macro...

  10. #10
    Registered User
    Join Date
    12-28-2017
    Location
    London
    MS-Off Ver
    2011
    Posts
    12

    Re: Macro to auto populate template from excel spreadsheet

    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

  11. #11
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,672

    Re: Macro to auto populate template from excel spreadsheet

    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.
    So you want me to list both projects on same invoice with individual totals...What about the different invoice no's as per column H

    What do you mean
    At the moment it is only returning the one for RWA-17-18-05.
    That is a single entry only for August. RWA-17-18-01 is a duplicate for August

    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

  12. #12
    Registered User
    Join Date
    12-28-2017
    Location
    London
    MS-Off Ver
    2011
    Posts
    12

    Re: Macro to auto populate template from excel spreadsheet

    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

  13. #13
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,672

    Re: Macro to auto populate template from excel spreadsheet

    Change this line
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    and it will render 4 invoices

    Edit
    Add this part
    Please Login or Register  to view this content.
    just below
    Please Login or Register  to view this content.
    Last edited by sintek; 12-28-2017 at 09:30 AM.

  14. #14
    Registered User
    Join Date
    12-28-2017
    Location
    London
    MS-Off Ver
    2011
    Posts
    12

    Re: Macro to auto populate template from excel spreadsheet

    Thats great! thanks for all your help!

  15. #15
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,672

    Re: Macro to auto populate template from excel spreadsheet

    Pleasure...Tx for rep +

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,342

    Re: Macro to auto populate template from excel spreadsheet

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  17. #17
    Registered User
    Join Date
    12-28-2017
    Location
    London
    MS-Off Ver
    2011
    Posts
    12

    Re: Macro to auto populate template from excel spreadsheet

    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?

  18. #18
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,672

    Re: Macro to auto populate template from excel spreadsheet

    Yes, Column 5...What error is it throwing. Works on upload.

  19. #19
    Registered User
    Join Date
    12-28-2017
    Location
    London
    MS-Off Ver
    2011
    Posts
    12

    Re: Macro to auto populate template from excel spreadsheet

    The error is:-

    Run-time error '1004':
    Autofilter method of range class failed

  20. #20
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,672

    Re: Macro to auto populate template from excel spreadsheet

    Post the code you are using or a sample of the current scenario...must have changes since last upload?

  21. #21
    Registered User
    Join Date
    12-28-2017
    Location
    London
    MS-Off Ver
    2011
    Posts
    12

    Re: Macro to auto populate template from excel spreadsheet

    I have attached the spreadsheet I am now using with the following code:-

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Cantona166; 12-30-2017 at 08:18 AM.

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,342

    Re: Macro to auto populate template from excel spreadsheet

    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)

  23. #23
    Registered User
    Join Date
    12-28-2017
    Location
    London
    MS-Off Ver
    2011
    Posts
    12

    Re: Macro to auto populate template from excel spreadsheet

    Sorry very new to this forum, hope its ok now

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,342

    Re: Macro to auto populate template from excel spreadsheet

    Yes - thanks.

  25. #25
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,672

    Re: Macro to auto populate template from excel spreadsheet

    Please Login or Register  to view this content.

  26. #26
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Macro to auto populate template from excel spreadsheet

    @ 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.

  27. #27
    Registered User
    Join Date
    12-28-2017
    Location
    London
    MS-Off Ver
    2011
    Posts
    12

    Re: Macro to auto populate template from excel spreadsheet

    Perfect!

    Thank you

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,342

    Re: Macro to auto populate template from excel spreadsheet

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  29. #29
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,672

    Re: Macro to auto populate template from excel spreadsheet

    Glad you got it sorted...

    @ bakerman2 Noted, tx

+ 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. Replies: 4
    Last Post: 02-03-2015, 05:55 AM
  2. Macro to auto-populate word template with bookmarks
    By DellRussellHWS in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-03-2014, 04:33 PM
  3. VBA Code to auto populate a fax template based critera's
    By sara101 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 08-14-2014, 10:56 AM
  4. New sheet based on template to auto populate column in summary page
    By RichTea88 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-05-2014, 07:52 AM
  5. Replies: 6
    Last Post: 09-12-2013, 04:16 AM
  6. Macro to populate a word template and feed an excel master sheet
    By hieldy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-05-2013, 05:40 AM
  7. Replies: 1
    Last Post: 03-14-2011, 02:28 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