将特定工作表的可变数组打印到单个打印输出 [英] Print Variable Array of Specific Worksheets to a Single Printout

问题描述

我想将Excel工作簿中的特定工作表打印到单个打印输出中.我的工作簿中还有不应打印的其他工作表.

I would like to print specific worksheets from my Excel workbook to a single printout. There are additional sheets in my workbook that should not print.

要打印的第一个工作表的名称将是常数(配方").其他要打印的工作表的名称将有所不同(它们都将带有前缀"Pie"和不同的后缀).根据项目的不同，要打印的页数也会有所不同.

The name of the first worksheet to be printed will be constant ("Recipe"). The names of the other worksheets to be printed will vary (they will all have the prefix "Pie" and different suffixes). The number of sheets to be printed will vary from project to project.

例如，
在项目1中，工作簿将包含以下工作表:配方"，派-苹果"，派-南瓜"，成分"和工具".

For example,
In project #1 the workbook will include the following worksheets: "Recipe", "Pie - Apple", "Pie - Pumpkin", "Ingredients", and "Tools".

在项目2中，该工作簿将包含以下工作表:食谱"，馅饼-卡士达"，馅饼-苹果"，馅饼-酸橙"，馅饼-蓝莓"，成分"和工具".

In project #2 the workbook will include the following worksheets: "Recipe", "Pie - Custard", "Pie - Apple", "Pie - Key Lime", "Pie - Blueberry", "Ingredients", and "Tools".

我想将配方"工作表以及所有以饼"开头的工作表打印到一个打印输出中.以下代码使我可以在整理其余工作表的同时合并所需的工作表:

I would like to print the "Recipe" worksheet and every worksheet that starts with "Pie" to a single printout. The following code allows me to unitize the desired worksheets while weeding out the rest:

Sub PrintArrayOfWorksheets()
Dim PrintCollection
PrintCollection = Array("Recipe", "Pie - Apple", "Pie - Pumpkin")
ThisWorkbook.Worksheets(PrintCollection).PrintOut
End Sub

此代码有效；但是，这需要我为每个项目手动输入数组的元素.请推荐一种自动更新阵列的方法，以包括满足上述条件的工作表.或者，请完全推荐其他方法.

This code works; however, it requires me to manually enter the elements of the array for each project. Please recommend a method of automatically updating the array to include the worksheets that meet the criteria above. Or, please recommend some other method entirely.

我尝试了几种不同的失败想法，如果需要，我可以分享.

I've tried a couple different unsuccessful ideas, and I can share those if requested.

亲切的问候，
托尼

Kind regards,
Tony

推荐答案

我在您的代码中添加了一段代码，该代码使用符合要求的工作表名称动态填充您的数组.

I have added a piece of code in yours that dynamically populates your array with the name of the sheets that fits the requirements.

Sub PrintArrayOfWorksheets()
    Dim PrintCollection() As String
    Dim WS As Worksheet
    Dim i As Integer

    ReDim PrintCollection(0 To 0)
    i = 0

    'Dynamically populates array
    For Each WS In ActiveWorkbook.Worksheets
        If LCase(WS.Name) = "recipe" Or LCase(Left(WS.Name, 3)) = "pie" Then
            ReDim Preserve PrintCollection(0 To i)
            PrintCollection(UBound(PrintCollection)) = WS.Name
            i = i + 1
        End If
    Next

    ThisWorkbook.Worksheets(PrintCollection).PrintOut
End Sub

这篇关于将特定工作表的可变数组打印到单个打印输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！