Results 1 to 7 of 7

Problem with macro that works on multiple workbooks

Threaded View

  1. #1
    Registered User
    Join Date
    04-12-2007
    Posts
    4

    Problem with macro that works on multiple workbooks

    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!
    Last edited by VBA Noob; 04-12-2007 at 01:51 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1