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:

    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.


    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.

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




    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
    With ws.Range("D7:AH87")

    I changed this too

    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!