How to get macros to work when changing sheet names in Excel

Question

I made a macro that deletes data within a cell range in 20 sheets. I did this before I named the sheets so I was having subscript errors as the macro is referring to sheet names that don't exist any more.

I've opted to use the codeName e.g Sheet4 as this should never change (as someone else suggested on another question). However the macro breaks as soon as it hits this part of the code.

Sheet4.Range("TimeSheet4[[Start Time]:[Holiday]]").Select
Selection.ClearContents
Sheet5.Range("TimeSheet45[[Start Time]:[Holiday]]").Select
Selection.ClearContents
Sheet6.Range("TimeSheet456[[Start Time]:[Holiday]]").Select
Selection.ClearContents
Sheet7.Range("TimeSheet4567[[Start Time]:[Holiday]]").Select
Selection.ClearContents

It was done like this previously and worked but I was intending to tidy the code.

Sheets("Employee 2 ").Select
Range("TimeSheet4[[Start Time]:[Holiday]]").Select
Selection.ClearContents
Sheets("Employee 3").Select
Range("TimeSheet45[[Start Time]:[Holiday]]").Select
Selection.ClearContents
Sheets("Employee 4").Select
Range("TimeSheet456[[Start Time]:[Holiday]]").Select
Selection.ClearContents
Sheets("Employee 5").Select
Range("TimeSheet4567[[Start Time]:[Holiday]]").Select
Selection.ClearContents

`First of all i'm new to VBA but i'm enjoying messing about with it.`In that case I would highly recommend reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Siddharth Rout, May 12 '21 at 20:06
yeah its the same table in the same format on each sheet, basically the macro just clears down all the data and creates a empty clear file ready to be worked on - rotas and timesheets — Ashley Wallace, May 12 '21 at 20:29

VBasic2008 · Answer 1 · 2021-05-13T01:20:16.880

Clear First Tables

If each table is the first table in each worksheet, the following will do the job.

Option Explicit

Sub clearFirstTables()
    
    Const Cols As String = "[[Start Time]:[Holiday]]"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet
    
    For Each ws In wb.Worksheets
        On Error Resume Next
        ws.Range(ws.ListObjects(1).Name & Cols).ClearContents
        On Error GoTo 0
    Next ws

End Sub

Or you could use this if there are several per worksheet or if they're not the first tables:

Sub clearAllTables()
    
    Const Cols As String = "[[Start Time]:[Holiday]]"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet
    Dim tbl As ListObject
    
    For Each ws In wb.Worksheets
        For Each tbl In ws.ListObjects
            On Error Resume Next
            ws.Range(tbl.Name & Cols).ClearContents
            On Error GoTo 0
        Next tbl
    Next ws

End Sub

How to get macros to work when changing sheet names in Excel

1 Answers1

Clear First Tables