News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Posting to Register an invoice general info work, but how about list items in a table?

    Posted on WSabouya Comment on the AskWoody Lounge

    Home Forums AskWoody support Productivity software by function Visual Basic for Applications Posting to Register an invoice general info work, but how about list items in a table?

    This topic contains 8 replies, has 2 voices, and was last updated by

     zeddy 2 years, 3 months ago.

    • Author
      Posts
    • #508288 Reply

      WSabouya
      AskWoody Lounger

      Hello,

      I would love to be able to save not only Invoice general header information, but details in bottom table as well. I attached a sample file for this purpose. the Module is called PostToRegister and it does copy few cells when user click Post to register button on the spreadsheet. I found it difficult to copy an entire table if cells are filled after/underneath the general header info! is there a way to do this? i tried many times but each time i thing about a way to do this i just get stuck.

      sample file: 46812-SavingInvoices

      Thank you.

      Attachments:
    • #1592208 Reply

      zeddy
      AskWoody_MVP

      Hi abouya

      In your sample file in post#1, you have 8 column headings on your sheet [Register].
      But your existing code is for 9 values. (e.g. what is in cell B17????)
      This code could also be simplified.

      It is not clear what you want you want to do.
      The table, based on your headers in row 18 on sheet [Invoice], has 11 columns.

      Perhaps if you could show us a ‘before’ and ‘after’ example, we could help you with the vba code to achieve this.

      zeddy

      • #1592216 Reply

        WSabouya
        AskWoody Lounger

        Hi abouya

        In your sample file in post#1, you have 8 column headings on your sheet [Register].
        But your existing code is for 9 values. (e.g. what is in cell B17????)
        This code could also be simplified.

        It is not clear what you want you want to do.
        The table, based on your headers in row 18 on sheet [Invoice], has 11 columns.

        Perhaps if you could show us a ‘before’ and ‘after’ example, we could help you with the vba code to achieve this.

        zeddy

        Thanks Zeddy,

        I corrected the B17 cell! what i’m looking for is to get the invoice information: like date, invoice number, company, project number etc… then be able to save what’s on the table (detailed information about each item/line in the invoice. Please look at the Register spreadsheet, i added an example that shows what i’m trying to achieve. each time i post to register, invoice general info and line details (the table) gets saved, then a color line to divide between each record (Invoice). I attached the sample file as well as another file that i found online that does what i’m looking for. I tried to adapt the code to my spreadsheet but it doesn’t want to work.

        Thanks again Zeddy.

        Sample file: 46819-SavingInvoices
        File that has something similar and I will love to get it to work on my project: https://1drv.ms/x/s!Ant3qnFMXUKYmlcZCWyCCT3tbV3I

        Attachments:
        • #1592269 Reply

          zeddy
          AskWoody_MVP

          Hi abouya

          OK, I see your example.
          I’ll write some vba to do your posting, and post back.

          zeddy

          • #1592270 Reply

            WSabouya
            AskWoody Lounger

            That will be my dream come true. Thank you so much. That file i sent you is very complicated, i tried to mimic the vba but i coul’t get it to work.

            • #1592292 Reply

              zeddy
              AskWoody_MVP

              Hi abouya

              Here’s the updated file.

              To simplify things, I used a named range of cells [invoiceRecord] for copying the Invoice record to the [Register] sheet.

              This named range is in hidden row 1.

              I also added some other named ranges to make it easier.
              You should be able to find them!

              I also added a check to make sure you couldn’t post the same Invoice Number to the [Register] sheet. You didn’t ask for this, but it makes sense to me! You will see a message if you try and post the same invoice number.

              You should be able to follow my vba code. I added lots of comments in the code.
              If you have any questions, please ask.

              zeddy

              Attachments:
            • #1592304 Reply

              WSabouya
              AskWoody Lounger

              Zeddy,

              This is the most advanced and well written code i even seen. You are a genius. very elegant way to think and to code.

              Thanks a lot.

              :thewave: :cheers:

            • #1594299 Reply

              WSabouya
              AskWoody Lounger

              I used the file in excel 2013 instead of 2010 and now i get many errors:

              Runtime error 1004: select method of range class failed.
              pasteSpecialof range class failed error

              The code works with f8 but clicking the button in main sheet doesnt.
              I don’t understand why this happening. also, the code doesn’t save nothing in the register either, only the first general info not the actual table (invoce line(s) details). the code is supposed to work, but i can’t understand why changing to excel 2013 will cause all this errors.

              Any help is appreciated.

              Thanks a lot.

            • #1594303 Reply

              zeddy
              AskWoody_MVP

              Hi abouya

              Runtime error 1004: select method of range class failed.
              This error means that your vba routine cannot find the thing you want to Select.
              For example, if you had..
              [Invoicenumber].select
              ..then you must have a named range Invoicenumber.
              Check by using the name-dropdown (to left of formula bar).
              It could also be because you are not on the correct sheet.
              For example, if you are copying between sheets, make sure your vba starts on the correct sheet for your .Copy command, and then moves to the correct sheet for your pasteSpecial command.

              zeddy

    • #1592484 Reply

      WSabouya
      AskWoody Lounger

      Hi Zeddy,

      so instead of using the spreadsheet to find invoice numbers and info, using a listbox will be more efficient.
      Is there a way to add the saved invoices to a listbox?

      Thanks.

      • #1592521 Reply

        zeddy
        AskWoody_MVP

        Hi abouya

        If you have a search in the Lounge, you will find a brilliant piece by Maud which does ‘filter-as-you-type’ to drill-down to matching records.

        zeddy

        • #1592522 Reply

          WSabouya
          AskWoody Lounger

          Zeddy,

          This is related to the method/code you showed me on how to copy values and invoice list to a register. The register is no a usual table, since a whole range of the invoice table was copied under the general invoice info. i was thinking for a way to add those values in range to a listbox directrly at the same time they get posted in the register!

          I tried Maud code but the advanced filter keeps resetting itself to reference the whole data in the range instead of colum B that have invoice number!

          Thanks.

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Posting to Register an invoice general info work, but how about list items in a table?

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.