Problem with macro that works on multiple workbooks

[vandalizmx]

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...

------

 
Sub FAMEVal()  'This is the macro that pastes the links as values and saves as a nother filename

For Each w In Workbooks
    For Each ws In Worksheets
        ws.Activate 'Select Worksheet
        Application.Run (["ValueData"]) 'Run macro
    Next ws 'Next Worksheet
Next w 'Next workbook

For Each w In Workbooks
        fname = Application.GetSaveAsFilename(filefilter:="Excel Files (*.xls), *.xls")
        ActiveWorkbook.SaveAs Filename:=fname
Next w

End Sub

-----

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]

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

Dim w As Workbook, ws As Worksheet

For Each w In Application.Workbooks
    For Each ws In w.Worksheets
        ws.Activate: Rem don't use this line unless ValueData requires it
        Application.Run (["ValueData"])
    Next ws
Next w

For Each w In Application.Workbooks
    fname = Application.GetSaveAsFilename(filefilter:="Excel Files (*.xls), *.xls")
    w.SaveAs Filename:=fname
Next w

[vandalizmx]

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]

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]

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:

Sub ValueWorkbooks()
Dim w As Workbook, ws As Worksheet, fName As String
Application.ScreenUpdating = False
For Each w In Application.Workbooks
    If w.Windows(1).Visible = False Then GoTo nextw 'fixes the hidden workbook issue
    For Each ws In w.Worksheets
        ws.Cells.Copy
        ws.Cells.PasteSpecial xlPasteValues
    Next ws

    fName = Application.GetSaveAsFilename("Valued " & w.Name, filefilter:="Excel Files (*.xls), *.xls")
    If fName = "False" Then GoTo nextw
    w.SaveAs Filename:=fName

nextw:
Next w
Application.ScreenUpdating = True
End Sub

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.

[vandalizmx]

Excellent. Thanks alot man, that code worked! I cant thank you enough, cheers!