在一个打印作业Excel VBA中选择并打印多张纸 [英] Select and print multiple sheets in one print job Excel VBA

问题描述

我已经继承了这种可怕性，被要求进行更新。

目前，此工作簿是由一张有一个复选框的工作表构成的，全部与工作簿中的其他工作表相关联。用户选择复选框，然后点击一个按钮打印与选中的每个复选框相关联的工作表。

现在构建的方式，它为每个选定的工作表运行打印作业。我被要求为所选的所有工作表运行一个打印作业（以避免有一百张封面）。

我已经写了一点VBA功能生成一个字符串，其中包含检查复选框的每个工作表的名称，以引号，逗号分隔。

我需要找出一种方法来使用此信息来选择所有的纸张，然后打印一次我需要的每张纸。

甚至地狱，我可以把这个字符串吐回原来的宏写为打印能力。现在宏是工作簿中的另一个工作表，并有102个不同的打印命令，由一堆if语句控制。所以我会把这个字符串吐到那张表中，以便我可以运行一个打印命令。

无论哪种方式，有人请帮忙。

这是我的代码：

 公共功能sheetString（）
 Dim c As整数
 Dim r As Integer 
 Dim sConcat As String 
 
 Dim ws As Worksheet 
 
对于c = 2至6步骤2 
对于r = 1 To 46 
如果Sheet94.Cells（r，c）= True然后
 sConcat = sConcat& Sheet94.Cells（r，c-1）& 
 End If 
 Next r 
 Next c 
 
 sConcat = Left（sConcat，Len（sConcat）） -  2）
 
 Debug.Print sConcat 
结束函数
  

代码的输出是这样（随着变化PR015，PR018，PR019，PR026，PR029A，PR018，PR018

编辑：感谢simoco，我比以前更近了。这是现在的代码。

 公共功能sheetString（）
 Dim c As Integer 
 Dim r As Integer 
 Dim sConcat As String 
 Dim ws As Worksheet 
 
设置ws = Sheet94 
 
对于c = 2至6步骤2 
对于r = 1 To 46 
如果ws.Cells（r，c）= True则
 sConcat = sConcat& ws.Cells（r，c-1）& 
 End If 
 Next r 
下一步c 
 sConcat = Left（sConcat，Len（sConcat）） -  1）
 Debug.Print sConcat 
 sheetString = sConcat 
结束函数
 
子测试（）
'如果表格名称的单元格包含引号
'表格（拆分（sheetString， ，），，））。选择
'如果具有工作表名称的单元格不包含引号
表格（Split（sheetString，，））选择
 ActiveSheet。 PrintOut副本：= 1 
 End Sub 
  

它没有轰炸，但现在只是

解决方案

这是稍微修改的功能 sheetString

 公共函数sheetString（）
 Dim c As Integer，r As Integer 
 Dim sConcat As String 
 Dim ws As Worksheet 
 
设置ws = Sheet94 
 
对于c = 2至6步骤2 
对于r = 1到46 
如果ws.Cells（r，c）然后
 sConcat = sConcat& ws.Cells（r，c-1）& ，
 End If 
 Next r 
 Next c 
 
 sConcat = Left（sConcat，Len（sConcat）） -  1）
 
 Debug.Print sConcat 
 sheetString = sConcat 
结束函数
  

然后调用use它像这样：

 子测试（）
'如果单元格的表格名称包含引号
' （Split（Replace（sheetString，），，））。PrintOut副本：= 1 
'如果具有工作表名称的单元格不包含引号
表格（Split printOut副本：= 1 
 End Sub 
  

I have inherited this awfulness, and been asked to make updates.

Currently this workbook is built with one sheet that has a bunch of checkboxes, all associated with other sheets in the workbook. The user selects checkboxes, and then hits a button to print the sheets associated with each checkbox that is checked.

The way it's built now, it runs a print job for each sheet selected. I have been asked to run one print job for all sheets selected (to avoid having a hundred cover sheets).

I have written a little VBA function that produces a string containing the name of every sheet for which the checkbox is checked, in quotes, comma separated.

I need to figure out a way to use this information to select all the sheets and then print once each sheet I need is selected.

Or even hell, I'd take being able to spit this string back into the macro that was originally written for the print ability. Right now the macro is another sheet in the workbook, and has 102 different print commands, controlled by a bunch of if statements. So I'd take being about to spit the string into that sheet so I could run one print command.

Either way, someone please help.

Here is my code:

Public Function sheetString()
    Dim c As Integer
    Dim r As Integer
    Dim sConcat As String

    Dim ws As Worksheet

    For c = 2 To 6 Step 2
        For r = 1 To 46
            If Sheet94.Cells(r, c) = True Then
               sConcat = sConcat & Sheet94.Cells(r, c - 1) & ", "
            End If
        Next r
    Next c

    sConcat = Left(sConcat, Len(sConcat) - 2)

    Debug.Print sConcat
End Function

The output of the code is like this (with varying names depending on which boxes are checked):

"PR015", "PR018", "PR019", "PR026", "PR029A"

EDIT: Thanks to simoco, I'm closer than I've been so far. Here is the code as it stands now.

Public Function sheetString()
    Dim c As Integer
    Dim r As Integer
    Dim sConcat As String
    Dim ws As Worksheet

    Set ws = Sheet94

    For c = 2 To 6 Step 2
        For r = 1 To 46
            If ws.Cells(r, c) = True Then
               sConcat = sConcat & ws.Cells(r, c - 1) & ","
            End If
        Next r
    Next c
    sConcat = Left(sConcat, Len(sConcat) - 1)
    Debug.Print sConcat
    sheetString = sConcat
End Function

Sub test()
    'if cells with sheet names contains quotes
    'Sheets(Split(Replace(sheetString, """", ""), ",")).Select
    'if cells with sheet names doesn't contain quotes
    Sheets(Split(sheetString, ",")).Select
    ActiveSheet.PrintOut Copies:=1
End Sub

It's running without bombing out, but now it's only selecting the first sheet that has it's box checked.

解决方案

Here is slightly modified function sheetString:

Public Function sheetString()
    Dim c As Integer, r As Integer
    Dim sConcat As String
    Dim ws As Worksheet

    Set ws = Sheet94

    For c = 2 To 6 Step 2
        For r = 1 To 46
            If ws.Cells(r, c) Then
               sConcat = sConcat & ws.Cells(r, c - 1) & ","
            End If
        Next r
    Next c

    sConcat = Left(sConcat, Len(sConcat) - 1)

    Debug.Print sConcat
    sheetString = sConcat
End Function

and then call use it like this:

Sub test()
    'if cells with sheet names contains quotes
    'Sheets(Split(Replace(sheetString, """", ""), ",")).PrintOut Copies:=1
    'if cells with sheet names doesn't contain quotes
    Sheets(Split(sheetString, ",")).PrintOut Copies:=1
End Sub

这篇关于在一个打印作业Excel VBA中选择并打印多张纸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！