如何在新工作表上选择任何表?(VBA)

Question

第一次发帖，真正的VBA初学者。

我试图写一个宏，改变到下一个工作表(本质上是ctrl + pg dn函数)，选择并突出显示表中的值，然后清除内容。

我遇到的问题是宏似乎只选择了一个表，从那里记录了宏，而不是继续选择新的工作表上的其他表。

我怎么能得到的代码选择下一个表从一个工作表?

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

Answer 1

清空Excel表(ListObject)列

主要

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

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 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

意外(主要分析)

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

Answer 2

您可以将此作为学习"true"的良好起点。VBA(非宏录制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

代码检查每个工作表，并在每个工作表上检查每个表(= listobject)。

然后它获得周日和周六的列索引，从要清除的范围检索(= DataBodyRange，因为你只想清除数据部分-不是头我假设)

最后一步是清除范围的内容。

(附加提示:阅读如何避免使用选择和如何避免复制/粘贴会给你更多的见解。)