How do I select any table on a new sheet? (VBA)

Question

First time posting and real beginner in VBA.

I was trying to write a macro that changes to the next sheet down (Essentially ctrl + pg dn function), selects and highlights values within a table and then clears the contents.

The problem I am running into is the macro only seems to select one table from where the macros was recorded - rather than continuing to select other tables on new sheets.

How can I get the code to select the next table from one sheet down?

Sub New_Hours()
'
' New_Hours Macro
' Moves down a sheet and removes previous hours
'
' Keyboard Shortcut: Ctrl+Shift+L
'
    ActiveSheet.Next.Select
    Range("Table13456789101112131415166188[[Sunday]:[Saturday]]").Select >> excel highlighted this as    the problem
    Selection.ClearContents
    Range("E9").Select
End Sub

Does the new worksheet, the one to the right, have just a single table? Do you really need it selected or can we just clear the contents? Are you trying to clear the tables on multiple worksheets to the right of the active sheet? — VBasic2008, Jan 09 '23 at 10:14

VBasic2008 · Answer 1 · 2023-01-09T11:31:42.753

Clear Excel Table (`ListObject`) Columns

Main

Sub NewHours()
'
' New_Hours Macro
' Moves down a sheet and removes previous hours
'
' Keyboard Shortcut: Ctrl+Shift+L
'
    On Error GoTo ClearError
    
    With ActiveSheet.Next
        If ClearBetweenTableColumns(.ListObjects(1), "Sunday", "Saturday") Then
            Application.Goto .Range("E9")
        End If
    End With

ProcExit:
    Exit Sub
ClearError:
    'Debug.Print "Run-time error '" & Err.Number & "': " & Err.Description
    Resume ProcExit
End Sub

The Method

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Clears the contents between two columns of an Excel table.
'               Returns a boolean indicating whether it was successful.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function ClearBetweenTableColumns( _
    ByVal Table As ListObject, _
    ByVal StartColumnID As Variant, _
    ByVal EndColumnID As Variant) _
As Boolean
    On Error GoTo ClearError

    With Table
        Dim sCol As Long: sCol = .ListColumns(StartColumnID).Index
        Dim eCol As Long: eCol = .ListColumns(EndColumnID).Index
        Dim cCount As Long: cCount = eCol - sCol + 1
        .DataBodyRange.Resize(, cCount).Offset(, sCol - 1).ClearContents
    End With

    ClearBetweenTableColumns = True

ProcExit:
    Exit Function
ClearError:
    'Debug.Print "Run-time error '" & Err.Number & "': " & Err.Description
    Resume ProcExit
End Function

Surprises (Main Analized)

Sub NewHoursEDU()

    If ActiveSheet Is Nothing Then Exit Sub ' no visible workbooks open
    If ActiveSheet.Index = ActiveSheet.Parent.Sheets.Count Then Exit Sub ' last

    Dim sh As Object: Set sh = ActiveSheet.Next
    If Not TypeOf sh Is Worksheet Then Exit Sub ' not a worksheet
    If sh.ListObjects.Count = 0 Then Exit Sub ' no table

    If ClearBetweenTableColumns(sh.ListObjects(1), "Sunday", "Saturday") Then
        Application.Goto sh.Range("E9")
    'Else ' an error occurred in the called procedure; do nothing!?
    End If

End Sub

Ike · Answer 2 · 2023-01-09T08:57:26.657

You can use this as a good starting point to learn "true" VBA (not macro recorded VBA):

Public Sub clearHours()

Dim ws As Worksheet
Dim lo As ListObject
Dim columnSunday As Long, columnSaturday As Long
Dim rgToClear As Range

For Each ws In ThisWorkbook.Worksheets
    For Each lo In ws.ListObjects
        With lo
            columnSunday = .ListColumns("Sunday").Index
            columnSaturday = lo.ListColumns("Saturday").Index
            
            Set rgToClear = .ListColumns(columnSunday).DataBodyRange.Resize(, columnSaturday - columnSunday + 1)
            rgToClear.ClearContents
            
        End With
    Next
Next

End Sub

The code checks each worksheet - and on each worksheet each table (= listobject).

Then it gets column indices of Sunday and Saturday to retrieve from that the range to be cleared (= DataBodyRange as you only want to clear the data part - not the header I assume)

Last step is clearing the contents of the range.

(add-on tip: reading How to avoid using select and How to avoid copy/paste will give you some more insights.)

How do I select any table on a new sheet? (VBA)

2 Answers2

Clear Excel Table (ListObject) Columns

Clear Excel Table (`ListObject`) Columns