Need help with a clear contents macro

tley

New Member
Joined
May 31, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi,

I am trying to create a macro to delete the contents of cells B2:H16 on all sheets except the Totals Sheet and the Student Template sheet. The other issue is the names of the sheets and the number of sheets is ever changing.

This spreadsheet is tracking student tasks each week. The teacher will track progress and reset it each week to prepare for the next weeks assignments. Based on completed tasks, the students will come and go making the names on each sheet change on occasion and the number of students attending with grow and shrink at times.

Is there a way to do this?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,915
Office Version
  1. 365
Platform
  1. Windows
The following macro will loop through each worksheet within the workbook running the program, and clear the contents for B2:H16 on each worksheet, except the 'Totals Sheet' and the 'Student Template' sheet...

VBA Code:
Sub Clear_Contents_Sheets()

    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Totals Sheet" And ws.Name <> "Student Template" Then
            ws.Range("B2:H16").ClearContents
        End If
    Next ws
    
End Sub

Hope this helps!
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,317
VBA Code:
Option Explicit

' Except Totals Sheet and the Student Template

Sub ClearRngeAllSheetsExcept()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each ws In Application.ActiveWorkbook.Worksheets
        If ws.Name <> "Totals" And ws.Name <> "Student Template" Then
            ws.Range("B2:H16").ClearContents
        End If
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,609
Messages
5,765,408
Members
425,284
Latest member
fishymuffin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top