时间戳

首页 > 解决方案 > 为所有工作表重复一个宏

excel - 为所有工作表重复一个宏

问题描述

Sub Format()

Dim LastRow As Long

Columns("A:E").Select
Range("A3").Activate
Columns("A:E").EntireColumn.AutoFit
Rows("7:7").Select
Selection.Delete Shift:=xlUp
Range("B16").Select

LastRow = Range("D9").End(xlDown).Row
Cells(LastRow + 1, "D").Formula = "=SUM(D9:D" & LastRow & ")"

End Sub

我正在处理需要向具有相同结构的不同数量数据表的个人报告的工作表。如果我需要为所有工作表重复代码怎么办。我是 stackoverflow 的追随者，从这个论坛学到了很多东西。

谢谢你凯沙夫

标签： excelvba

解决方案

遍历工作表

Sub Format()

    Dim LastRow As Long, sh As Worksheet

    For Each sh In Sheets
        With sh
            .Columns("A:E").EntireColumn.AutoFit
            .Rows("7:7").Delete Shift:=xlUp
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            .Cells(LastRow + 1, "D").Formula = "=SUM(D9:D" & LastRow & ")"
        End With
    Next sh
End Sub

循环浏览除一张以外的工作表

Sub LoopSheetsExceptOne()
    Dim LastRow As Long, sh As Worksheet, ws As Worksheet

    Set ws = Sheets(1)

    For Each sh In Sheets

        If sh.Name <> ws.Name Then

            With sh
                .Columns("A:E").EntireColumn.AutoFit
                .Rows("7:7").Delete Shift:=xlUp
                LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                .Cells(LastRow + 1, "D").Formula = "=SUM(D9:D" & LastRow & ")"
            End With

        End If

    Next sh

End Sub

推荐阅读