VBA为工作簿中的特定工作表设置了一个循环 [英] VBA set a Loop for specific worksheets in a workbook

问题描述

我运行一个月度报告，该报告会生成16个选项卡(15个工作表:"Report1"-"Report15").我创建了一个子表来创建/格式化表格，并在Sheet2("Report1")上组织数据.

I run a monthly report that generates 16 tabs (15 worksheets: "Report1" - "Report15"). I've created a sub to create/format a table, and organize the data on Sheet2("Report1").

目标:由于表样式的原因，我现在想仅通过"Report1"，"Report4"，"Report7"，"Report10"，"Report13"来循环宏.

Objective: Because of the Table Style, I would now like to loop the macro through "Report1", "Report4", "Report7", "Report10", "Report13" Only.

(一旦我弄清楚了，我将为其他工作表创建一个具有其他表格样式的宏.)

(Once I figure this out, I'll create a Macro with another Table Style for the other worksheets.)

问题:通过谷歌搜索"，我创建了下面的循环，但是"Set ws = Worksheets(Report1)ws.active抛出了该循环.-我需要删除集合ws = worksheets(Report1)吗?

Issues: Through 'Googling' I created the below Loop, but the "Set ws = Worksheets(Report1") ws.active is throwing it off. -Do I need to remove the set ws = worksheets(Report1")?

-我有ws.active，因为没有它，宏似乎无法工作.

-I had the ws.active, because the macro didn't seem to work without it.

宏:

Option Explicit

Sub LoopThroughSpecificWorksheets()

'Turn Off Screen Updates
Application.ScreenUpdating = False
Application.DisplayAlerts = False

    Dim wb As Workbook: Set wb = ActiveWorkbook
    Dim ws As Worksheet
    Dim LstObj As ListObjects
    Dim LastRow As Long
    Dim Report, i

    Report = Array("Report1", "Report4", "Report7", "Report10", "Report13")

    For i = LBound(Report) To unbound(Report)
        With ws(Report(i))

    Set ws = Worksheets("Report1")
    ws.Activate

'...Body of Maco
'Insert Table
'Remove Table Format
'Apply Tablestyle:
'Apply a filter to $ Share for all Brands (Largest to Smallest)
'Update $ - % Chg formula
'Update Units - % Chg Formula
'Change Header Names and Resize


    End With
Next i

'Turn On Screen Updates
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

推荐答案

下面是一些循环工作表数组的VBA代码:

Below is some VBA code that loops your array of worksheets:

Sub sLoopArray()
    Dim ws As Worksheet
    Dim aReport As Variant
    Dim lngLoop1 As Long
    aReport = Array("Report1", "Report2")
    For lngLoop1 = LBound(aReport) To UBound(aReport)
        Set ws = Worksheets(aReport(lngLoop1))
        With ws


        End With
    Next lngLoop1
End Sub

我已将 unbound 的类型更改为 UBound ，将循环计数器声明为Long(您将其作为可能导致问题的变体)，并重命名了从 Report 到 aReport 的数组(以避免与任何内置VBA名称发生冲突".

I've changed the type of unbound to UBound, declared the loop counter as Long (you had it as a variant which can cause problems), and also renamed the array from Report to aReport (to avoid "collision" with any inbuilt VBA names.

此致

这篇关于VBA为工作簿中的特定工作表设置了一个循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！