using macros to clear

[k9lover]

I am using the microsoft 2007 and I am trying to clear certain cells out the field from multiple worksheet off of one workbook.

For example:

The following worksheet names: Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday. On each of the worksheet I am trying to clear out the fields: B1, B4:B46 and H4:H46.

I tried several different formulas in the macros and none of them work. The clear field is located in the Monday worksheet.

Can you help me?

 

[CWatts]

Try this:

Sub ClearCells()
    Sheets(Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")).Select
    Range("B1,B4:B46,H4:H46").ClearContents
End Sub

I don't know what you mean by "the clear field" though. Are you trying to clear the cells, or copy something in there?

 

[hiker95]

k9lover,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ClearSheetRanges()
' hiker95, 07/21/2011
' http://www.mrexcel.com/forum/showthread.php?t=565833
Dim ws As Worksheet
Dim wary, a As Long
Application.ScreenUpdating = False
wary = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
For a = LBound(wary) To UBound(wary)
  Set ws = Worksheets(wary(a))
  ws.Range("B1,B4:B46,H4:H46").Clear
Next a
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ClearSheetRanges macro.