Need to edit macro in multiple workbooks to output data to master workbook

**batador** · 07-07-2013, 03:41 PM

Ok, I will try to explain as best I can. I have a workbook with multiple sheets in it. I have a macro in this workbook that looks at all the sheets and an sql server and puts data into a single sheet within the workbook. I want to make copies of this workbook and distribute it to three of my co-workers. When they click a button I have inserted into the workbook to run the macro, I want all of our data to output to a master workbook. I do not know how to script the file path name and what not into the macro. I can't post the workbook due to network security restrictions here at work so I will answer any follow up questions as best I can and I will copy and paste the code directly into the forum space. Any and all help is greatly appreciated.

Please Login or Register  to view this content.

**batador** · 07-11-2013, 05:37 PM

Ok, so I learned how to open another workbook from inside VBA. Now everything works pretty well until I get to where I copy my data in one workbook and try and paste it into the other master workbook. I receive an error that says:

Run-time error '1004':
Insert Method of Range of Class Failed

It also highlights exactly where it is failing. The code that is highlighted is some code that runs perfectly well within the script before it has to go out and open the master workbook. I'm a failing to understand. Anyone got any ideas?

**batador** · 07-16-2013, 05:05 PM

I have moved beyond the method of range class failed problem and have now encountered a PasteSpecial Method of Range Class Failed error now. I think what is happening is that by the time I get my master workbook opened and try to paste the copied data, the copied data isn't there to paste anymore. I think I may need to re-write the code somehow, but I am not sure how to write it. Can anyone take a look and point me in the right direction? And I apologize for the sloppy coding, still very green with this and I am learning. I have highlighted in red where I get the error message.

Please Login or Register  to view this content.

**xladept** · 07-16-2013, 07:24 PM

Hi Batador,

I've rewritten much of your routine - see if it still works:

Please Login or Register  to view this content.

**batador** · 07-16-2013, 08:00 PM

Re-written much hell! You re-wrote the whole thing! We went from riding in a Pinto to a Ferrari right there! It looks great and works great too. Thank you so very much. It looks really clean and I can actually decipher what you did. I feel ashamed now even posting what I had cobbled together. I can learn a lot from this. I will mark as solved as soon as I figure out how to! Thanks again!