Unable to apply code to 2 workbooks

  • Hello All, i am very new to VBA but i am working my way through, slowly. The code below, NOT MINE, works great between 2 different sheets within the same workbook. Can the code be applied to work between 2 workbooks? I have tried but i am getting to the point where i believe it is not designed for the purpose i have in mind. Any help would be very much appreciated.



  • It would be easier to help and test possible solutions is you could attach copies of your actual files with a detailed explanation of what you want to do using a few examples from your data and referring to specific cells, sheets and workbooks.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Good Afternoon Mumps,
    Thank you for the response and direction. Please see below and attached.
    I have attached two files - Master Doc and CSV. I would like the macro to start at the top of Cell A1 in Master Doc, recognize the value of that cell, then search the entire column A of the CSV doc for that value, and if it is present, move to A2 of Master Doc, recognize the value, and then search the entire column A of the CSV doc for that value. If the value is not in that column, i would like it to highlight that Cell, A2 of the master doc. Once it has gone through the whole column A of the Master doc, i would like it to do the exact opposite in the CSV, if it is present, no change, if not in the Master doc, highlight the cell in the CSV.


    In the CSV doc attached, if you run the code Compare_Col(), it works perfectly and as i would like it, but i want to treat the second sheet as another workbook.


    I appreciate the help this far, and if anything else is needed that would allow you or anyone to help in anyway, please let me know. I keep coming up with Conditional Formatting and VLookup, but i THINK it will not work in this scenario, the code i have is great, just need to know if i can apply to two different workbooks, not 2 different sheets in the same workbook.

  • Make sure that both workbooks are open and try:

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Mumps,
    This is exactly what i am looking for!! I do appreciate the help very much. I will be spending the next while learning exactly what you did. As i said, very new to it, but my interest level continues to grow. Thank you again!


    Thank you,
    Andrew

  • You are very welcome. :) Here is the code with added explanatory comments. I hope this helps.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • My pleasure. :)

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Mumps,
    As instructed, necessary files attached to run macro. Also, a description of my current problem.
    1) initial code is


    Dim WS1 as Worksheet
    Dim WS2 as worksheet


    I would like to Dim WS1/2 as Strings so i can enter in textbox values from userform. However, when i Dim WS1 (& WS2) as String, WS1 (& WS2) receive an error message of INVALID QUALIFIER. I would like to use variables as i want the code to run all the way through after the user fills out the form completely. The end goal is to have the CSV on the left side (with highlighted cells, if any, and on the right side of the screen, the PM501/(s) with highlighted cells, if any. This would allow the user to review the CSV & PM501/(s) for any discrepancies.


    The CSV Creator & Reconciliation code is unlocked and i think might bring a laugh or two due to my VERY limited coding skills, but i would really like this work somehow. Any help or direction would be appreciated.


    one would open the CSV & Reconciliation doc, load the user form and then click "Create CSV". Merging the CSVs can be ignored for the problem that i am working on at the moment (NO)


    I appreciate the help. The next post has the master doc attached as only 2 attachments can be sent per post

  • I'm still not clear on what you want to do. This I my understanding so far. You have multiple CSV files. Each CSV file has one worksheet with data in column A. You want to compare the data in column A of each CSV file to the data in column A of the Master file and highlight the cells in each file that don't exist in the other. Is this correct? I'm not sure where WS1 and WS2 come in. You say that the Master file contains multiple sheets. Does column A in all these sheets contain data that you want to compare? What are PM501/(s)?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Good Morning Mumps,
    I apologize for the confusion. There is only 1 CSV file that will check itself against (1 and up to 5) master docs while highlighting the cells in the Master doc/(s) if not in the CSV. Then, in the CSV, cells will highlight if not found in the Master doc (or master docs). The WS1 and WS2 is from your code:


    Dim WS1 as Worksheet


    I am unable to reference a variable from the userform when WS1/2 is a Worksheet, not a string. When I Dim as WS1 as String, the variable WS1/2 comes back as an invalid qualifier.


    As far as the Master doc/(s) containing multiple sheets, i was trying to express that i would have to call the Video Checklist sheet. Only the Video Checklist tab will be reconciled against.


    PM501 is the name of the Master doc

  • Instead of defining WS1 as a string, define it as a worksheet and then you can reference it using WS1.Name.
    I think I have a better understanding now. Do you want to be prompted to select each Master file or if the Masters are saved in one specific folder, do you want to loop through all of them and do the comparison in each one? If you want to loop through them, what is the full path to the folder that contains them and what is their extension (xls,xlsm,xlsx)? When you say "The end goal is to have the CSV on the left side (with highlighted cells, if any, and on the right side of the screen, the PM501/(s) with highlighted cells, if any." I assume you want this to happen in each master file. Is this correct?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Understood on referencing by WS1.Name - I will give that a try in the meantime. Can the .Name be a variable from the Userform?
    There will not be a need to prompt to select each master file as the user fills that information in at the beginning in the Userform - they are asked to select how many Master Docs there are and where their locations are.
    I would like to loop through however many the user selects - up to 5
    Their extensions will all be .xlsm
    Yes, i would like this to happen for each master file - on the let side of the screen there will be the CSV with/without highlighted cells, and on the right there may be 5, all the way down to 1, master docs open with/without highlighted cells.

  • WS1.Name will return the name of the worksheet. You can define a variable such as wsName as String and then have it reference the sheet name: wsName=WS1.Name.
    It looks like you want to incorporate the macro into the code for your user form. This makes it difficult for me because whenever you try to incorporate new code with existing code, it rarely works the way you expect. The user form code is lengthy and involves many references which makes it hard for me to follow. Rather than trying to modify your user form code, I can perhaps suggest some steps you could take. The macro I suggested works to compare the data between sheets in two files or 2 columns in the same workshet . I don't think that having the CSV cells on the left side with 1 to 5 master docs on the right all on one sheet would work because the highlighting of the CSV cells would be different for each Master file. You would have to have the comparison done on one sheet per Master file where each sheet would have the same CSV cells in column A and the cells from each Master in column B. Then the macro could loop through each sheet to do the comparison and highlight the differences in each column. The file would look something like the one I attached.

    Files

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thank you Mumps,
    I understand your concern and i greatly appreciate your help this far. This has helped me understand more about VBA than you can imagine.

  • You are very welcome. I hope it all works out for you. :)

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!