Problem with macro that works on multiple workbooks

**vandalizmx** · 04-12-2007, 01:42 PM

Hello, and thanks for taking the time to read this and try to give me a hand

. I am working on a macro that will select all the data on ALL sheets in ALL open workbooks and copy it. Then paste it as values, and save a backup... as not to overwrite the original (which is linked for easy updating).

I have everything working just fine, but for some reason the macro runs 2 times on the first workbook, all the rest are fine. The problem with that is, if you try to overwrite the file while the macro is running it will crash it (it has an error with the variable fname, which holds the filename, and which cant be overwritten.), and I have to be able to allow many people to use it so it has to be user friendly. I have a feeling it is because 'Personal.xls' is technically always open, so maybe it is just doing the first book 2 times do to that? Here is the code that I am having trouble with...

------

Please Login or Register  to view this content.

-----

Say I have 5 workbooks open. The macro will run 6 times... twice on the FIRST workbook, and then one time on the others (as desired). I am trying to get rid of the uncessary loop it does on the first workbook. I hope I make sense, let me know if you need more details.

Thanks again!

**mikerickson** · 04-12-2007, 03:19 PM

Using ActveCell, Activate and Select is fraught with problems.

I'm not sure what your value data does, but you might prefer looping this way

Please Login or Register  to view this content.

**vandalizmx** · 04-13-2007, 10:17 AM

Well, I still have the same problem. If I dont use "w.activate" to select the workbook then it will not process it. It only works on that first one over and over again. Any other ideas? Im still poking it to see if I can get it going correctly.

**vandalizmx** · 04-13-2007, 10:22 AM

Sorry for the quick double post, but I am almost 100% sure that Personal.xls is causing the problem. I need to use the .Activate function otherwise it doesnt select the other workbooks/sheets, it just does the task on that one sheet/book a whole bunch of times. There must be a way to make sure that it doesnt sense Personal.xls.

**wes t** · 04-13-2007, 11:08 AM

This may be a little overkill, but I added a few things to your code for error awareness (if someone doesn't want to save the valued workbook for some reason) and I'm not sure what your [ValueData] code does, but I used two lines to convert the sheets to values, I imagine you can still use your code by replacing those lines:

Please Login or Register  to view this content.

The reason you need the "w.Activate" is that your other code, ValueData, will work on whatever worksheet is currently active unless you pass it a worksheet variable and use that. Anyways, let me know if my code helps.