Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: VBA Match to Send invoice Number to Cell in Template based on printed status

  1. #1
    Join Date
    13th April 2012
    Posts
    3

    VBA Match to Send invoice Number to Cell in Template based on printed status

    Hi All,

    I had trouble coming up with title name, sorry.

    I have a big project that i am working on and i am stuck on an issue.

    I have a workbook...sample extract attached...which has three worksheets.

    "Details" - Contains invoice information in rows...each invoice number may have multiple rows depending on items invoiced
    "Invoice" - Invoice template gets populated by entering invoice number from "Details" sheet column A
    "Customers" - Contains Customer information.

    I have got a module macro function in my main workbook that copies the template to its own workbook, saves a pdf and emails each invoice once a command button beside the invoice is pressed and this works fine.

    Basically my problem is that I want to click a command button that when clicked, searches column Q of "Details" worksheet and finds each instance of ´nvoices that are not printed (not printed).
    Once the command finds a not printed value, it grabs the invoice number from column A of the "Details" sheet and places this invoice number into cell H11 of the "Invoice" worksheet template.
    This will then populate the invoice worksheet (a step that I do manually). Once this is done I want it to launch my macro to copy print and email pdf. Once this process is completed it goes on to search for the next instance of "not printed".

    Im guessing this will be vba which I am just new at learning. I am find with attaching code to command buttons and creating userforms etc, just need help with the code. The problem I am running into is the fact that a single invoice may have many rows or a single row.

    Thanks for your help on this one!
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,442

    Re: VBA Match to Send invoice Number to Cell in Template based on printed status

    In the Form use

    =MAX(Details!A4:A9)+1

    Beter to Format the Invoce details data as a table or create a Dynamic named Range to use in the formula
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  3. #3
    Join Date
    13th April 2012
    Posts
    3

    Re: VBA Match to Send invoice Number to Cell in Template based on printed status

    Hi Roy,

    Thanks for your reply. Thats the formula I am going to use to auto generate my next invoice number, but what I want to do is lookup column Q and if the result is not printed, I would like the vba to post the invoice number that is not printed into cell 'Invoice'$H$11 of the invoice template (to load that invoice data into the template - the data migration works fine if I manually enter the invoice number into cell 'Invoice'$H$11).

    Once this invoice number is placed into the template, i want my macro (named CopySend() Macro) to run. Once this is done I then want the vba to change printed status to "printed" and then look for the next invoice number that is referenced as "Not Printed" and do the same.

    I am new to VBA and struggling to get my head around a VBA solution.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,442

    Re: VBA Match to Send invoice Number to Cell in Template based on printed status

    Use the .Find method & .FindNext, both are well documented in the VBA help files
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  5. #5
    Join Date
    13th April 2012
    Posts
    3

    Re: VBA Match to Send invoice Number to Cell in Template based on printed status

    Hi Roy, thanks for pointing me for this one.

    I have solved this question

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 1
    Last Post: August 12th, 2011, 09:55
  2. Replies: 2
    Last Post: November 2nd, 2010, 20:53
  3. Replies: 5
    Last Post: September 6th, 2010, 22:56

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
  •  
porno