Posts by bpdarrow

    I see, that's my fault. I should have added that in my original post. So I have 9 source workbooks that are updated monthly with new data. The idea is to store the macro in Personal.XLSB so that it can be run with any source file. The files contain the dealer data by region, and the "dealer code" filter criteria will be specific to whichever region that store is located. Once the data for the specific dealer code is added to a new workbook, I have then recorded formatting macros for each of the 5 sheets. Can the formatting macros be added to this filter and export code? Or is it better to run them separately?

    Well, I just got it to work. I'm 100% it's my fault - just working through it now. I originally inserted a module to my PERSONAL.XLSB file and pasted your code there. Then I opened the file that I put in here. Then ran it, and that's where I got the error. After your last reply, I opened the sample file and saved it as XLSM rather than XLSX. I inserted a module to the new XLSM version, and pasted the code into the new module. Then I ran it - and it worked. Is the issue because the macro is stored in the XLSB workbook? Must it be stored in the master file?

    I downloaded the file that I uploaded here just in case and ran it again. Still get that error, debug takes me to this part of of the autofilter line 26. When it stops running, it's stopped on the new open workbook that doesn't have any data in it yet. Do I need to set a range? I just copied/pasted into a new module as you had it.


    Hi Roy, thanks for response. Apologies for not posting code in the correct place. I'm trying to go back and edit my original message to remove it, but having trouble. I've also included an example of one of the master files that I'm working with (there are 9 total master files). Also a .txt file with the script that I was able to get working to do what I was trying, but it seems cumbersome and is reliant on two file names (that will in reality be different every time) to function.

    Here is my dilemma. I have a master workbook with 5 sheets. Each sheet contains a different data-set, but will be filtered by a "Dealer Code" criteria that is common to all 5 sheets. This "Dealer Code" criteria is Column A in all 5 sheets. My goal is to apply autofilter to all 5 sheets, insert a dealer code criteria into a filter input box, copy the filtered data from each sheet, create a new workbook, prompt the Save As dialog box to open, and then paste the filtered data into 5 new sheets. I've had limited success with copying 1 tab and performing the rest of the functions, but not with all 5. I can get all 5 to paste to 1 new book and sheet, but I'm stuck here.