Run Code On Mulitple Sheets

  • I have a workbook that provides updates to our timesheet workbook. For some reason, I cannot get the code to execute the update. It doesn't crash, and appears to run fine, yet the update doesn't occur.


    Can someone see where I am going wrong here?


  • Re: Run Code On Mulitple Sheets


    You've not referenced ws when referring to Range("D7:AH87"), so that will always point to the range on the active sheet, not ws.

  • Re: Run Code On Mulitple Sheets


    the code runs, but doesn't make any of the changes (ie. adding the data validation on all the ws in each of the workbooks.)


    as a matter of fact, it appears to simply open the files, then save and close them. almost as if its jumping right over the code.


    I am going to reactivate screen updating and step through.

  • Re: Run Code On Mulitple Sheets



    My guess is that it's expecting this instead:

    Code
    Dim ws As Worksheet
    
    
    For Each ws In ActiveWorkbook.Worksheets


    The For Each construct works on each item of a collection, so your ws needs to be Dimmed as a single Worksheet. Also, all worksheets are sheets, but not all sheets are worksheets, so you could run into another problem if there are chart sheets in the workbook.


    Brian

    >DISABLE DALEK WITH SONIC SCREWDRIVER
    The batteries have gone flat. I told you that you should have changed them after you spent ten hours debugging it in the TARDIS. But did you listen? Noooooooo...


    The Dalek notices your presence for the first time, and reacts normally.
    "EXTERMINATE! EXTERMINATE!"


    *** You have been exterminated ***

  • Re: Run Code On Mulitple Sheets


    Thanks. It seems to operate now on a test update. going to run it for real now and see how it goes.


    I also changed...


    Code
    activeworkbook


    to....


    Code
    wbresults


    not sure if this is really necessary, but it seemed to help amidst the other things i was trying. thanks for your help guys. will reply back with results if it works.


    Thanks Again!!

  • Re: Run Code On Mulitple Sheets


    Quote from norie
    Code
    With ws.Range("D7:AH87")


    I changed this too

    Code
    With ws.Range("D7:AH87").validation


    Sure it was just an oversight on your part.


    Worked fantastic gentlemen....I am humbled by your wisdom and knowledge once again.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!