Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    New users should read the Forum Rules before posting

    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.

    Comment


    • #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.

      Comment


      • #4
        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

        New users should read the Forum Rules before posting

        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.

        Comment


        • #5
          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

          Comment

          Working...
          X