Run a macro on 2 sheets (Excel 2003)

Topic

New Reply

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(“p17:p18”).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”

Reply | Quote

Replies

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”).ClearContents

Reply | Quote

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

Reply | Quote

See post 249,920 for links to VBA tutorials.

See the thread starting at post 435353 for links about books.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote