Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Run a macro on 2 sheets (Excel 2003)

    I have some code that will delete data on Sheet A. Can someone tell me how to 'extend' this code to also delete data (in different ranges) from Sheet B? I suspect that I have to write something for Sheet B (which would be inactive at the moment that the macro is run, b/c it is a different sheet....ps: can somone (Hans?) suggest a good text on VBA....that will explain it and is readable?

    The code is have so far is this:

    ActiveSheet.Unprotect Password:="open"
    Range("Q3:T5").Select
    Selection.ClearContents
    Range("P13:P14").Select
    Selection.ClearContents
    Range("P15:P16").Select
    Selection.ClearContents
    Range("p1718").Select
    Selection.ClearContents
    Range("B23:F42").Select
    Selection.ClearContents
    Range("I23:I42").Select
    Selection.ClearContents
    Range("J23:N42").Select
    Selection.ClearContents
    Range("P23:P42").Select
    Selection.ClearContents
    Range("V23:V42").Select
    Selection.ClearContents
    Range("X23:X42").Select
    Selection.ClearContents
    Selection.ClearContents
    Range("c16").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingRows:=True, AllowInsertingRows:=True, Password:="open"

  2. #2
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Run a macro on 2 sheets (Excel 2003)

    Thank you for that....I learn something each time I come here...ps: any suggestions on a good text about VBA coding?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run a macro on 2 sheets (Excel 2003)

    See <post:=249,920>post 249,920</post:> for links to VBA tutorials.

    See the thread starting at <post#=435353>post 435353</post#> for links about books.

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro on 2 sheets (Excel 2003)

    I'm sure the links that Hans has suggested will bring abundant information. My main resource,other than the Lounge, is "Excel 2000 VBA Programmer's Reference" published by WROX. I think they have a 2003 edition as well.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Run a macro on 2 sheets (Excel 2003)

    I also have that book, and it is excellent, but with due respect, I would suggest that if you like reference books, as well as or in preference to web citations (I use both, and I suspect that most other people do as well) you might be better off with John Walkenbach's "Power Programming in Excel XXXX with VBA" (where XXXX coresponds to the version you are using, or higher). The "Programmer's Reference" is pretty heavy going, compared to the level of question you are asking. I have no doubt it will assist you; but I suspect that Walkenbach will be quicker and easier for now: the Programmer's Reference will be more use when you have a little more experience.
    One caveat about the Programmer's Reference: when WROX published the Excel 2003 version, the four authors of the original were not involved, and all of them disclaimed any responsibility for that version - at one time at least one of them had some unflattering remarks about the editing and accuracy of the material in the book on their website. WROX has now published an Excel 2007 version, and three of the original "Excel 2000" authors (along with a fourth whose name escapes me at the moment) participated in it - from personal corespondence with one of the common authors, they all consider the "2007" version as the 'real second edition' of the '2000' original.

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro on 2 sheets (Excel 2003)

    I agree the level of the book is much heavier than the question posed, but I did not pose the question. I merely responded to the question.

    Hopefully the OP will see your suggestion concerning John W's books.

  7. #7
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro on 2 sheets (Excel 2003)

    You do not need to select each range you want to clear.

    Instead of:
    Range("Q3:T5").Select
    Selection.ClearContents

    You can use:
    Range("Q3:T5").ClearContents


    To do the clearing on another sheet you can use:
    Worksheets("SheetName").Range("Q3:T5").ClearConten ts

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Run a macro on 2 sheets (Excel 2003)

    I see, after looking back at the thread - my apologies for my confusion!

Posting Permissions

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