☰

创建宏来打印选定的单元格

tag-icon • tag-icon microsoft-excel macros
创建宏来打印选定的单元格

我正在尝试录制Macro以打印选定的单元格，然后将工作表恢复为原始格式。我隐藏了 B 到 H 列，打印A7:I68。录制宏时，打印结果正确。保存后，它仅打印 A7:A68 列，不包括 I 列。

ActiveSheet.Unprotect
Columns("B:H").Select
Selection.EntireColumn.Hidden = True
Range("A7:I68").Select
ActiveSheet.PageSetup.PrintArea = "$A$7:$I$68"
Selection.PrintOut Copies:=1, Collate:=True
Columns("A:I").Select
Selection.EntireColumn.Hidden = False
ActiveWindow.SmallScroll Down:=-72
Range("A10").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
    False

有什么建议么？

答案1

蒂娜，我想建议使用 VBA 代码来打印不止一个而是多个选择，这将复制范围以进行打印并粘贴到那里，显示预览，打印并最终删除该工作表。

Sub Print_MultiSelection()

Dim rng As Range, ws As Worksheet, rngArea As Range

Set rng = Selection

Set ws = Worksheets.Add(After:=Sheets(Sheets.Count))

For Each rngArea In rng.Areas rngArea.Copy Destination:=ws.Range(rngArea.Address)

ws.Range(rngArea.Address).Value = rngArea.Value

Next 

rngArea ws.PrintPreview

ws.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:= False

Application.DisplayAlerts = False

ws.Delete Application.DisplayAlerts = True

rng.Parent.Activate

End Sub

答案2

快速问答，您的 ActiveSheet 中是否有一些隐藏区域？如果您在 PrintOut 行中指定 PrintArea，则可能会起作用。即

ActiveSheet.Unprotect
Columns("B:H").EntireColumn.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$7:$I$68"
ActiveSheet.PrintOut Copies:=1, Collate:=True, PrintArea:="$A$7:$I$68"
Columns("A:I").EntireColumn.Hidden = False
ActiveWindow.SmallScroll Down:=-72
Range("A10").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False

最重要的是，如果您想要更“复杂”的版本；）请尝试以下方法，希望对您有所帮助。添加的目的是处理隐范围。

Sub PrSelRng()
    Dim prRng As Range
    
    ActiveSheet.Unprotect
    On Error Resume Next

    Set prRng = Application.InputBox("Select the range to print", Type:=8)
    On Error GoTo 0
    
    If prRng Is Nothing Then
        MsgBox "Printing canceled.", vbInformation
        Exit Sub
    End If
    
    If Not prRng.Worksheet Is ActiveSheet Then
        MsgBox "Please select a range on the active sheet.", vbExclamation
        Exit Sub
    End If
    
    ActiveSheet.PageSetup.PrintArea = GetVisibleRange(prRng).Address
    ActiveSheet.PrintOut Copies:=1, Collate:=True
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False

End Sub

Function GetVisibleRange(rng As Range) As Range
    Dim cell As Range
    Dim visibleCells As Range

    For Each cell In rng
        If cell.EntireColumn.Hidden = False Then
            If visibleCells Is Nothing Then
                Set visibleCells = cell
            Else
                Set visibleCells = Union(visibleCells, cell)
            End If
        End If
    Next cell

    If Not visibleCells Is Nothing Then
        Set GetVisibleRange = visibleCells
    End If

End Function

相关内容