在VBA中跳过工作簿的第一个工作表
我想在工作簿中的每个工作表上都有许多工作表,跳过第一个工作表并执行一些格式化操作,但是我希望此vba代码跳过第一个工作表(名称可以不同,但总是会是第一)。因此,问题是我应该怎么做?在VBA中跳过工作簿的第一个工作表
Sub ex2()
Dim kl As Worksheet
Dim Ws_Count As Integer
Dim a As Integer
Ws_Count = ActiveWorkbook.Worksheets.Count
For a = 2 To Ws_Count
With Rows("2:2")
.RowHeight = 20
.Interior.Color = RGB(150, 250, 230)
End With
With Range("B2")
.Value = "Sheet Number" & " " & a
.Font.Size = 12
.Font.Bold = True
.Font.Underline = True
End With
Next a
End Sub
你的代码是好的,你只是错过了单行线,检查目前工作表kl.Index
。
代码
Option Explicit
Sub ex2()
Dim kl As Worksheet
For Each kl In Worksheets
' not the first worksheet
If kl.Index > 1 Then
With kl.rows("2:2")
.RowHeight = 20
.Interior.Color = RGB(150, 250, 230)
End With
With kl.Range("B2")
.Value = "Sheet Number" & " " & kl.Index - 1
.Font.Size = 12
.Font.Bold = True
.Font.Underline = True
End With
End If
Next kl
End Sub
非常感谢! – Mroweczka
@Mroweczka欢迎:) –
试试这个:
Sub ex2()
Dim Ws_Count As Integer
Dim a As Integer
Ws_Count = ActiveWorkbook.Worksheets.Count
For a = 2 To Ws_Count
With Worksheets(a)
'rest of your code
End With
Next a
End Sub
随着贴码,最终的结果将是:
Sub ex2()
Dim Ws_Count As Integer
Dim a As Integer
Ws_Count = ActiveWorkbook.Worksheets.Count
For a = 2 To Ws_Count
With Worksheets(a)
Worksheets(a).Activate
With Rows("2:2")
.RowHeight = 20
.Interior.Color = RGB(150, 250, 230)
End With
With Range("B2")
.Value = "Sheet Number" & " " & worksheets(a).Index - 1
.Font.Size = 12
.Font.Bold = True
.Font.Underline = True
End With
Next a
End Sub
遍历您的工作表这样的,并检查索引属性(存储工作表的位置),以确保它不是第一个。
Public Sub test()
For Each ws In Worksheets
If ws.Index > 1 Then
'Formatting goes here
End If
Next
End Sub
你几乎没有,因为你只错过了工作specification
你既可以增加一个后For a = 2 To Ws_Count
一个,或者是更好的正确或者添加Worksheets(a).Activate
声明,包住格式化代码在With Worksheets(a) ... End With
块,加入点(.
)每range
参考之前,并让它们指的是当前参考工作表,如下
Sub ex2()
Dim a As Integer
For a = 2 To Worksheets.Count
With Worksheets(a) '<--| reference current index worksheet
With .Rows("2:2") '<--| reference current worksheet row 2
.RowHeight = 20
.Interior.Color = RGB(150, 250, 230)
End With
With .Range("B2") '<--| reference current worksheet cell "B2"
.Value = "Sheet Number" & " " & a
.Font.Size = 12
.Font.Bold = True
.Font.Underline = True
End With
End With
Next a
End Sub
所以,没有必要进行任何If
声明,将只曾经工作过的:虽然它不会影响性能显著在这种情况下,这将是从纯粹的编码的观点非常低效
检查我的代码我的答案在下面 - 这是你的95% –