+ Reply to Thread
Results 1 to 8 of 8

Excel 2007 : Matching across Worksheets & Inserting Data

  1. #1
    Registered User
    Join Date
    12-09-2010
    Location
    Cannock, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Matching across Worksheets & Inserting Data

    Hi,

    I have inherited some workbooks/worksheets & having a personal crash course in Excel, to amend these to my needs, I am using Excel 7 !!! One area where I am stuck:-

    I have one workbook with 2 worksheets. The first worksheet is formatted as my Companies invoice, the second worksheet contains the data to complete the invoice (1 row per Customer).

    I select whichever Customer (using the Customer Number) I want from the data worksheet & copy & paste the Customer Number to the invoice worksheet. The remainder of the invoice is then populated (using Vlookup commands, matching on Customer Number) & pulling all corresponding data from the data worksheet.

    I then run a macro using a Control Key, which actions a few other functions & finishing with printing of the invoice.

    I now want to recognise which Customers have had their invoice printed when looking back at the data worksheet.

    I have added a column in the data worksheet called "printed" & currently manually insert the word "yes" for the Customers invoice as each one is printed, but I am sure there could be a way to automate this?

    As a specification, I want to add a function to the print invoice macro, that would use the Customer Number being printed on the current invoice, find & match the corresponding Customer Number in my data worksheet & for that same Customer (i.e on that same row), insert the word "yes" in the "printed" column.

    I have been looking at the Excel commands available & struggling, I am expecting perhaps a combination of commands and can they run within a macro ?!?!!?

    Would really appreciate any help or direction on this.

    Many Thanks

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Matching across Worksheets & Inserting Data

    Hi,

    Please upload a sample workbook - anonymised if necessary.

    Rgds
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    12-09-2010
    Location
    Cannock, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Matching across Worksheets & Inserting Data

    Hi,

    Here is a test version, with personal data removed.

    The invoice skeleton work sheet is the 1st one called "invoice", the data work sheet is the last one called "gas info".

    In my description of problem I quoted Customer Number to help make sense of my description in reality as you will see in worksheets it is "Job Number", ( FYI there is a Customer Number, but not used.

    If you go into "gas info" work sheet & copy a Job Number then paste that into "invoice" work sheet into job number data field (the field between Customer & SAP No), you then process macro CTRL-R (macro 2).

    You will see where I have manually entered some "yes" against a couple of line entries, in "gas info" worksheet, this is what I am looking to automate at end of CTRL-R (macros2).

    I have removed a couple of worksheets (they had personal data), that should have no impact on the above.

    As you will see I have inherited some messy coding, it took me ages to see what is doing what ?!?! but decided to leave alone, 1. if it aint broke leave it! 2. I am still learning !!

    Cheers

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Matching across Worksheets & Inserting Data

    Hi,

    See the attached. I've added a new macro called 'UpdateGasInfo'. I've also added an instruction to call this macro as the last line of your print macro.

    As you indicated the macros could do with a lot of tidying up!

    I find the easiest way to log invoice lines like this is to keep a dedicated 'New Record' range of cells in exactly the same order as the Gas Info sheet layout, and reference each of these cells to the Invoice cell which contains the data. Then when you want to add a record you just need to copy the'new record' range to the next line in the recipient sheet.

    Another tips. Try and avoid merged cells as you had with the Job No. Merged cells are usually a complete pain.

    Rgds

  5. #5
    Registered User
    Join Date
    12-09-2010
    Location
    Cannock, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Matching across Worksheets & Inserting Data

    Hi,

    Thanks for the amendment, I have just had chance to try it out. I am getting an error message: "Run-time error '1004' Method Range of Object _Global failed". When I went into debug mode it highlighted the line in macro that starts "1Row = Sheet7......"

    One thing need to mention, as I had sent you an example workbook with personal info missing (removing quite a bit of info including a worksheet within the workbook), this meant I took your amendments via copy & paste (your amendments being the new macro UpdatGasInfo & the one line that calls the macro) and input them into my current workbook, Is this Ok ?

    I also needed the one line calling the UpdateGasInfo macro in a different place.

    I would be happy to send you (Forum Moderator) the whole workbook with ALL info if this would make things easier BUT would this be on view to ANY person who views forum ?

    I am wary the info I removed to keep info private may be effecting your amendments ?!?!?

    Cheers

  6. #6
    Registered User
    Join Date
    12-09-2010
    Location
    Cannock, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Matching across Worksheets & Inserting Data

    Hi,

    Thanks for the amendment, I have just had chance to try it out. I am getting an error message: "Run-time error '1004' Method Range of Object _Global failed". When I went into debug mode it highlighted the line in macro that starts "1Row = Sheet7......"

    One thing need to mention, as I had sent you an example workbook with personal info missing (removing quite a bit of info including a worksheet within the workbook), this meant I took your amendments via copy & paste (your amendments being the new macro UpdatGasInfo & the one line that calls the macro) and input them into my current workbook, Is this Ok ?

    I also needed the one line calling the UpdateGasInfo macro in a different place.

    I would be happy to send you (Forum Moderator) the whole workbook with ALL info if this would make things easier BUT would this be on view to ANY person who views forum ?

    I am wary the info I removed to keep info private may be effecting your amendments ?!?!?

    Cheers

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Matching across Worksheets & Inserting Data

    Hi,

    Not sure what you mean by having the UpdateGasInfo in a different place, but of course any change you make to what I sent could affect the smooth running of the macro.

    Can't you just anonymise the personal data and upload the workbook. We don't need to see real names or indeed all names. Lots of 'Mickey Mouses' would do.

    Regards

  8. #8
    Registered User
    Join Date
    12-09-2010
    Location
    Cannock, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Matching across Worksheets & Inserting Data

    Hi,

    Attached is the Workbook with all Worksheets.

    FYI - Worksheet Customers did initially have 25 rows & Worksheet Total Invoices did initially have 523 rows, the remaining rows have been altered where necessary to hide private info. I don't think the reduction of data has any impact on testing ??

    As mentioned I did copy & paste of the GasUpdateInfo macro & the corresponding "call" of the macro from your version into my full version to test.

    The sequence of the processing to test is as follows:

    1. Copy a Job No from the Gas Info worksheet
    2. Paste this Job No into Job No on Invoice worksheet.
    3. CTRL&Q (processes Macro1 - this will print 2 copies of invoice & do some behind scenes processing).
    4. CTRL&R (processes Macro2 - this will put copy of invoice into PaperPort & do some more behind scenes processing).

    Problems:

    CTRL&Q not doing anything, get a bleep as if something wrong but no error messages.

    If I try CTRL&R in isolation that still works.

    I noted you mention that Job No on Invoice worksheet is safer as NOT being a merged cell, which obviously I will take your advice on that. But this has had impact of taking the font criteria from the "copied" Job No. (which can vary), when it is "pasted". Whereas when the Job No in the Invoice worksheet was set as merged, it always maintained the font criteria as set in the invoice worksheet (Aerial / Bold / size 12), regardless of the font criteria of the Job No being "copied & pasted". Is there anyway of retaining the font criteria of the Job No in the Invoice worksheet as well as NOT being merged cell?

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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