如何为VBA宏添加指定次数循环并执行打印预览？

你的问题核心是循环逻辑位置错误，且存在冗余重复操作，以下是修正后的完整代码：

Sub Calculate()
    ' Macro to copy data to a new row, run it a specified number of times, and print output.
    
    ' Turn off screen updating.
    Application.ScreenUpdating = False
    
    ' Declarations.
    Dim copySheet As Worksheet
    Dim pasteSheet As Worksheet
    Dim targetRange As Range
    Dim loopCount As Long
    Dim i As Long
    Dim printSheet As Worksheet
    
     ' Set variables (仅需初始化一次)
    Set copySheet = Worksheets("Sheet5")
    Set pasteSheet = Worksheets("Sheet6")
    Set printSheet = Worksheets("Sheet6")

    ' Get the loop count from Sheet3!A1 (提前获取循环次数)
    loopCount = Sheets("Sheet3").Range("A1").Value
    
    ' Unlock pasteSheet once before loop (循环前解锁一次即可)
    pasteSheet.Unprotect Password:="password"
    
    ' Loop from 1 to the specified loop count
    For i = 1 To loopCount
        ' 每次循环重新定位目标单元格（粘贴后最后一行会变化）
        Set targetRange = pasteSheet.Cells(pasteSheet.Rows.Count, 3).End(xlUp).Offset(1, 0)
        
        ' 找到第一个无格式条件的单元格
        Do Until targetRange.FormatConditions.Count = 0
            Set targetRange = targetRange.Offset(1, 0)
        Loop
        
        ' Copy range C22:M22.
        copySheet.Range("C22:M22").Copy
        
        ' Paste the copied range into targetRange.
        targetRange.PasteSpecial xlPasteAll
    Next i
    
    ' Lock pasteSheet after loop (循环结束后统一锁定)
    pasteSheet.Protect Password:="password"
    
    ' Unprotect and unhide printSheet.
    printSheet.Visible = xlSheetVisible
    printSheet.Unprotect Password:="password"
    
    ' Open print preview for the specified range.
    printSheet.Range("B1:N46").PrintPreview
    
    ' Hide and protect printSheet again.
    printSheet.Protect Password:="password"
    printSheet.Visible = xlSheetHidden
    
    ' Turn off the cut-copy mode.
    Application.CutCopyMode = False
    
    ' Turn on screen updating.
    Application.ScreenUpdating = True
End Sub

关键调整说明 #

• 把变量初始化、工作表解锁这类只需执行一次的操作移到循环外，避免重复消耗资源
• 将找目标单元格、复制粘贴的核心逻辑放进循环内部，确保每次循环都能正确定位新的目标行
• 移除了原代码中循环外的冗余复制粘贴操作，避免额外执行一次
• 循环前解锁工作表、循环后锁定，减少重复的保护/取消保护操作

内容的提问来源于stack exchange，提问作者user19368410