excel VBA -打印多个范围

pgky5nke  于 2022-11-26  发布在  其他
关注(0)|答案(1)|浏览(243)

下面是一个VBA代码,它可以帮助您从Excel中打印所需的范围,但不知道如何改进它以满足以下条件。当执行时,它将多个选择打印到一页中,但并不适合它们。在复制第一个选择后,第二个选择将出现在该页的后续行中,依此类推。例如,如果选择两个随机列(A和C),每个列包含10个值,作为要打印输出的所需范围,则第二个选择的列(C)将位于第一个列(A)的下方。是否可以对其进行修改,以便将第二个(和下一个选择)放置到第一个选择/列右侧的新列中?

'VBA Code
Sub printOutRange()
Dim xRng1 As Range
Dim xRng2 As Range
Dim xNewWs As Worksheet
Dim xWs As Worksheet
Dim xIndex As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xWs = ActiveSheet
Set xNewWs = Worksheets.Add
xWs.Select
xIndex = 1
For Each xRng2 In Selection.Areas
    xRng2.Copy
    Set xRng1 = xNewWs.Cells(xIndex, 1)
    xRng1.PasteSpecial xlPasteValues
    xRng1.PasteSpecial xlPasteFormats
    xIndex = xIndex + xRng2.Rows.Count
Next
xNewWs.Columns.AutoFit
xNewWs.PrintPreview
xNewWs.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
a6b3iqyw

a6b3iqyw1#

这应该可以做到:

Dim xRng1 As Range
Dim xRng2 As Range
Dim xNewWs As Worksheet
Dim xWs As Worksheet
Dim xIndex As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xWs = ActiveSheet
Set xNewWs = Worksheets.Add
xWs.Select
xIndex = 1
For Each xRng2 In Selection.Areas
    xRng2.Copy
    Set xRng1 = xNewWs.Cells(1, xIndex)
    xRng1.PasteSpecial xlPasteValues
    xRng1.PasteSpecial xlPasteFormats
    xIndex = xIndex + 1
Next
xNewWs.Columns.AutoFit
xNewWs.PrintPreview
xNewWs.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

相关问题