Hi Guys,

The code below is a portion of a macro which you guys help build, well
really you built for me, I did the copy and paste bit. Its purpose is that it
will allways print the sheet called "Opening Cover", and then only following
sheets when the IF statement is True.

Sub PrintButton_Click()

Sheets("opening cover").Select
On Error Resume Next Application.Dialogs(xlDialogPrint).Show
If Err.Number < 0 Then
Err.Clear
Exit Sub
End If
On Error GoTo 0

If Worksheets("Calcs").Range("i1") = True Then
Worksheets("sheet1").PrintOut Copies:=1
End If
If Worksheets("Calcs").Range("f1") = True Then
Worksheets("sheet2").PrintOut Copies:=1
End If
If Worksheets("Calcs").Range("f2") = True Then
Worksheets("sheet3").PrintOut Copies:=1
End If
If Worksheets("Calcs").Range("f2") = True Then
Worksheets("sheet4").PrintOut Copies:=1

Else
Sheets("opening cover").Select
Range("f13").Select

End If
End Sub


This works fine, but the document has risen to 43 worksheets, still this is
not a problem if printing to paper.

We are now trying to move aware from paper copies, so some of the guys who
have been testing this print to MS Office image printer, and some to acrobat,
when this happens each page that is printed, but each worksheet that is True
is saved as a seperate document. (Nightmare, ok in acrobat these can be
merged afterwards but still a pain).

Is it possible to alter the code above, such that when printing the
worksheets that are True, they are all selected in one hit, and then printed?

This way if printed as a file to be saved, it would be only one document,
rather than 43.


Hope that makes sense, I've had a try but lost?
--
This post was created using recycled electrons!

Beetle, try something like this:
Dim ShArr() As String
Dim ArrInit As Boolean
ArrInit = False
With Worksheets("Calcs")
If .Range("A2") = True Then
If Not ArrInit Then
ReDim ShArr(0)
ShArr(0) = "Sheet1"
Else
ReDim Preserve ShArr(ubound(ShArr)+1)
ShArr(ubound(ShArr)) = "Sheet1"
End If
End If
'Repeat for all sheets
Sheets(ShArr).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
--
Charles Chickering

"A good example is twice the value of good advice."


"Newbeetle" wrote:

Hi Guys,

The code below is a portion of a macro which you guys help build, well
really you built for me, I did the copy and paste bit. Its purpose is that it
will allways print the sheet called "Opening Cover", and then only following
sheets when the IF statement is True.

Sub PrintButton_Click()

Sheets("opening cover").Select
On Error Resume Next Application.Dialogs(xlDialogPrint).Show
If Err.Number < 0 Then
Err.Clear
Exit Sub
End If
On Error GoTo 0

If Worksheets("Calcs").Range("i1") = True Then
Worksheets("sheet1").PrintOut Copies:=1
End If
If Worksheets("Calcs").Range("f1") = True Then
Worksheets("sheet2").PrintOut Copies:=1
End If
If Worksheets("Calcs").Range("f2") = True Then
Worksheets("sheet3").PrintOut Copies:=1
End If
If Worksheets("Calcs").Range("f2") = True Then
Worksheets("sheet4").PrintOut Copies:=1

Else
Sheets("opening cover").Select
Range("f13").Select

End If
End Sub


This works fine, but the document has risen to 43 worksheets, still this is
not a problem if printing to paper.

We are now trying to move aware from paper copies, so some of the guys who
have been testing this print to MS Office image printer, and some to acrobat,
when this happens each page that is printed, but each worksheet that is True
is saved as a seperate document. (Nightmare, ok in acrobat these can be
merged afterwards but still a pain).

Is it possible to alter the code above, such that when printing the
worksheets that are True, they are all selected in one hit, and then printed?

This way if printed as a file to be saved, it would be only one document,
rather than 43.


Hope that makes sense, I've had a try but lost?
--
This post was created using recycled electrons!

I think I'd approach it slightly differently since you're up to 43 sheets that
can get printed.

I'd set up some sort of cross reference between the address to check and the
sheet to print. This could be in the worksheet or it could be in code. (I'm
gonna use code just because.)

After you have that, you can loop through that table of addresses and build an
array of sheetnames to print.

Kind of like:

Option Explicit
Sub PrintButton_Click()

Dim myAddresses As Variant
Dim mySheetNames As Variant
Dim myListToPrint() As String
Dim iCtr As Long
Dim pCtr As Long

myAddresses = Array("I1", "F1", "F2", "F2") 'F2 twice???
mySheetNames = Array("Sheet1", "sheet2", "Sheet3", "Sheet4")

If UBound(myAddresses) < UBound(mySheetNames) Then
MsgBox "Design error--Number of addresses don't match number of sheets!"
Exit Sub
End If

On Error Resume Next
Application.Dialogs(xlDialogPrint).Show
If Err.Number < 0 Then
Err.Clear
Exit Sub
End If
On Error GoTo 0

ReDim myListToPrint(LBound(myAddresses) - 1 To UBound(myAddresses))
'include "opening cover" as the first sheet to print
myListToPrint(LBound(myListToPrint)) = "Opening Cover"

pCtr = LBound(myAddresses) - 1
For iCtr = LBound(myAddresses) To UBound(myAddresses)
If Worksheets("calcs").Range(myAddresses(iCtr)).Value = True Then
pCtr = pCtr + 1
myListToPrint(pCtr) = mySheetNames(iCtr)
End If
Next iCtr

ReDim Preserve myListToPrint(LBound(myListToPrint) To pCtr)

If pCtr = LBound(myAddresses) Then
'found at least one sheet to print
'no need to print just "opening Cover" right?
Sheets(myListToPrint).PrintOut preview:=True
Else
MsgBox "No sheets to print!"
End If

End Sub

I have no idea how this will work with PDF files, though.

Newbeetle wrote:

Hi Guys,

The code below is a portion of a macro which you guys help build, well
really you built for me, I did the copy and paste bit. Its purpose is that it
will allways print the sheet called "Opening Cover", and then only following
sheets when the IF statement is True.

Sub PrintButton_Click()

Sheets("opening cover").Select
On Error Resume Next Application.Dialogs(xlDialogPrint).Show
If Err.Number < 0 Then
Err.Clear
Exit Sub
End If
On Error GoTo 0

If Worksheets("Calcs").Range("i1") = True Then
Worksheets("sheet1").PrintOut Copies:=1
End If
If Worksheets("Calcs").Range("f1") = True Then
Worksheets("sheet2").PrintOut Copies:=1
End If
If Worksheets("Calcs").Range("f2") = True Then
Worksheets("sheet3").PrintOut Copies:=1
End If
If Worksheets("Calcs").Range("f2") = True Then
Worksheets("sheet4").PrintOut Copies:=1

Else
Sheets("opening cover").Select
Range("f13").Select

End If
End Sub

This works fine, but the document has risen to 43 worksheets, still this is
not a problem if printing to paper.

We are now trying to move aware from paper copies, so some of the guys who
have been testing this print to MS Office image printer, and some to acrobat,
when this happens each page that is printed, but each worksheet that is True
is saved as a seperate document. (Nightmare, ok in acrobat these can be
merged afterwards but still a pain).

Is it possible to alter the code above, such that when printing the
worksheets that are True, they are all selected in one hit, and then printed?

This way if printed as a file to be saved, it would be only one document,
rather than 43.

Hope that makes sense, I've had a try but lost?
--
This post was created using recycled electrons!

--

Dave Peterson

Beetle, I had a slight error in my first code try this:
Dim ShArr() As String
Dim ArrInit As Boolean
ArrInit = False
With Worksheets("Calcs")
If .Range("A2") = True Then
If Not ArrInit Then
ReDim ShArr(0)
ShArr(0) = "Sheet1"
ArrInit = True
Else
ReDim Preserve ShArr(ubound(ShArr)+1)
ShArr(ubound(ShArr)) = "Sheet1"
End If
End If
'Repeat for all sheets
Sheets(ShArr).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1

--
Charles Chickering

"A good example is twice the value of good advice."


"Charles Chickering" wrote:

Beetle, try something like this:
Dim ShArr() As String
Dim ArrInit As Boolean
ArrInit = False
With Worksheets("Calcs")
If .Range("A2") = True Then
If Not ArrInit Then
ReDim ShArr(0)
ShArr(0) = "Sheet1"
Else
ReDim Preserve ShArr(ubound(ShArr)+1)
ShArr(ubound(ShArr)) = "Sheet1"
End If
End If
'Repeat for all sheets
Sheets(ShArr).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
--
Charles Chickering

"A good example is twice the value of good advice."


"Newbeetle" wrote:

Hi Guys,

The code below is a portion of a macro which you guys help build, well
really you built for me, I did the copy and paste bit. Its purpose is that it
will allways print the sheet called "Opening Cover", and then only following
sheets when the IF statement is True.

Sub PrintButton_Click()

Sheets("opening cover").Select
On Error Resume Next Application.Dialogs(xlDialogPrint).Show
If Err.Number < 0 Then
Err.Clear
Exit Sub
End If
On Error GoTo 0

If Worksheets("Calcs").Range("i1") = True Then
Worksheets("sheet1").PrintOut Copies:=1
End If
If Worksheets("Calcs").Range("f1") = True Then
Worksheets("sheet2").PrintOut Copies:=1
End If
If Worksheets("Calcs").Range("f2") = True Then
Worksheets("sheet3").PrintOut Copies:=1
End If
If Worksheets("Calcs").Range("f2") = True Then
Worksheets("sheet4").PrintOut Copies:=1

Else
Sheets("opening cover").Select
Range("f13").Select

End If
End Sub


This works fine, but the document has risen to 43 worksheets, still this is
not a problem if printing to paper.

We are now trying to move aware from paper copies, so some of the guys who
have been testing this print to MS Office image printer, and some to acrobat,
when this happens each page that is printed, but each worksheet that is True
is saved as a seperate document. (Nightmare, ok in acrobat these can be
merged afterwards but still a pain).

Is it possible to alter the code above, such that when printing the
worksheets that are True, they are all selected in one hit, and then printed?

This way if printed as a file to be saved, it would be only one document,
rather than 43.


Hope that makes sense, I've had a try but lost?
--
This post was created using recycled electrons!

Wow, code and a half!

Thanks for the information Guys, I will have a look this weekend,
Thanks again, your time is appreciated, and it keeps me head ticking.

Have a good weekend.


--
This post was created using recycled electrons!

Hi,

I got the code to work, and only had to bang my head twice, thanks for your
time guys, I couldnt have got there without the help.


--
This post was created using recycled electrons!


"Newbeetle" wrote:

Hi Guys,

The code below is a portion of a macro which you guys help build, well
really you built for me, I did the copy and paste bit. Its purpose is that it
will allways print the sheet called "Opening Cover", and then only following
sheets when the IF statement is True.

Sub PrintButton_Click()

Sheets("opening cover").Select
On Error Resume Next Application.Dialogs(xlDialogPrint).Show
If Err.Number < 0 Then
Err.Clear
Exit Sub
End If
On Error GoTo 0

If Worksheets("Calcs").Range("i1") = True Then
Worksheets("sheet1").PrintOut Copies:=1
End If
If Worksheets("Calcs").Range("f1") = True Then
Worksheets("sheet2").PrintOut Copies:=1
End If
If Worksheets("Calcs").Range("f2") = True Then
Worksheets("sheet3").PrintOut Copies:=1
End If
If Worksheets("Calcs").Range("f2") = True Then
Worksheets("sheet4").PrintOut Copies:=1

Else
Sheets("opening cover").Select
Range("f13").Select

End If
End Sub


This works fine, but the document has risen to 43 worksheets, still this is
not a problem if printing to paper.

We are now trying to move aware from paper copies, so some of the guys who
have been testing this print to MS Office image printer, and some to acrobat,
when this happens each page that is printed, but each worksheet that is True
is saved as a seperate document. (Nightmare, ok in acrobat these can be
merged afterwards but still a pain).

Is it possible to alter the code above, such that when printing the
worksheets that are True, they are all selected in one hit, and then printed?

This way if printed as a file to be saved, it would be only one document,
rather than 43.


Hope that makes sense, I've had a try but lost?
--
This post was created using recycled electrons!