Errors Keep Popping Up

  • Below is some code that I have:

    I keep getting errors, and I'm not sure what's wrong. I inserted a data validation drop-down box in "ABC-Quote to Customer-Testing_OzGrid" file in cell J1.

    So what I'm trying to do is have the user select and open the file from the vendor that we need, then they need to go to the drop-down box in "ABC-Quote To Customer-Testing_OzGrid" file and select the vendor from cell J1.
    Depending on which vendor (case) the user selects, will then determine which data to copy and paste to where.

    I need this because each vendor (case) format is different, but I need them all to copy and paste into the "ABC-Quote To Customer-Testing_OzGrid" file so that in the end they are all in a consistent format on the "ABC-Quote..." file.

    I need to get this done ASAP, but every time I fix an error, another one pops up. :( have attached the files!


  • Re: Errors Keep Popping Up

    Hi ekfasy

    I've got your code working & I presume it's putting the copied data where you want it. I haven't changed anything like that.
    It's easier if you select the file you want from the dropdown before you start the macro, you won't have to stop the routine to do the check. I put a test in to compare the file name you selected to open with the name in the cell J1. If the J1 text is in the filename selected, it will run the routine, if not it will terminate.

    The causes of the errors were

    1) Matching of hyphens "-" and underscores "_". You had hyphens in the dropdown and underscores in the file names. The file you selected from the file open window won't match Cell J1 causing the Select Case part of the macro to never run & routine would exit every time.

    2) When copying the data from the HIJ_123 etc workbook, you didn't fully qualify where the code was supposed to be copied from. You used ".Range(.Cells(11, 1), .Cells(z, 1)).Copy" when it needed to be "Wb2.Worksheets("Quote").Range(.Cells(11, 1), .Cells(z, 1)).Copy"

    I changed a couple of things to make the code more readable, any questions, gimme a shout. The code is below & the file is attached for you to check out
    If you have the editor open place a break just after myFile = application.blah blah by putting the mouse cursor on the left hand frame of the window, right next to the next line down & left click, select the file you want to open in J1, press F5 to start the macro, select the file to open & the macro will stop at the break. You can then step through the macro by pressing F8 to check out the code. Apologies if you already knew how to do all that :)


Participate now!

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