VBA Excel Page Page Break to Clans Change to to Workbook中的每个

Question

我有一些代码可以在单元格内容的更改中添加页面断开，但是我无法使其运行比活动表的更多。我有大约80张床单需要运行并需要同时运行。我尝试在此Workbook上运行它，但它不起作用。它的工作表可以很好地效果，但在整个工作簿上都不能。

Option Explicit
Sub Set_PageBreaks()
    Dim lastrow As Long, c As Range
    lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    Application.ScreenUpdating = False
    For Each c In Range("A2:A" & lastrow)
        If c.Offset(1, 0).Value <> c.Value And c.Offset(1, 0) <> "" Then
            c.Offset(1, 0).PageBreak = xlPageBreakManual
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

Answer 1

有点草率的解决方案（因为您不应该真正使用activate），但这应该起作用：

Option Explicit
Sub Set_PageBreaks()
Application.ScreenUpdating = False
Dim ws_count As Long, i as long, lastrow As Long, c As Range
ws_count = ThisWorkbook.Worksheets.Count
For i = 1 to ws_count
    ThisWorkbook.Sheets(i).Activate
    lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    For Each c In Range("A2:A" & lastrow)
        If c.Offset(1, 0).Value <> c.Value And c.Offset(1, 0) <> "" Then
            c.Offset(1, 0).PageBreak = xlPageBreakManual
        End If
    Next c
Next i
Application.ScreenUpdating = True
End Sub

Answer 2

这是我要处理您的问题的方式：

Option Explicit
Sub Set_PageBreaks()
    Dim Sheet As Worksheet, C As Range, lastrow As Long
    Call SpeedUpCode(True)
    For Each Sheet In ThisWorkbook.Sheets
        lastrow = Cells(Rows.Count, "B").End(xlUp).Row
        For Each C In Range("A2:A" & lastrow)
            If C.Offset(1, 0).Value <> C.Value And C.Offset(1, 0) <> "" Then
                C.Offset(1, 0).PageBreak = xlPageBreakManual
            End If
        Next C
    Next Sheet
    Call SpeedUpCode(False)
End Sub
Sub SpeedUpCode(ByVal Value As Boolean)
    With Application
        If Value = True Then
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
        ElseIf Value = False Then
            .ScreenUpdating = True
            .Calculation = xlCalculationAutomatic
        End If
    End With
End Sub