Selecting pages to print within worksheet

[Raiko]

Hello,

I am trying to make it so that based on a list of TRUE or FALSE values, one for each page, the print area is set to include a page for printing when the value is set to TRUE. Each of these pages have been defined as a Named Range to make it easier to adjust them later without coding.
The values TRUE or FALSE are not mandatory, but will make it easier for my boss to understand that the page is included :P

I thought this would be easy with some previous experience in C++ coding, but after eight hours of trying really hard i haven't even been able to make a simple Union command of three ranges show up as a print area in my document, or preferably in the same menu as the CTRL+P command.

As far as sample data for what i'm trying to do:

three named ranges:
page1 = (A1:B2)
page2 = (A4:B5)
page3 = (A7:B8)

Three cells:
K1 = TRUE
K2 = FALSE
K3 = TRUE

to give me a Print_Area of (A1:B2,A7:B8) where changing any of the I values changes the print area respectively.

This is what i've tried so far (approximately) even though it does not work.


Public Sub PrintOfferte()


Dim PrintRange, test1, test2, test3 As Range


Dim i As Integer
Dim testadres As Range


Set test4 = Sheets("Offerte").Range("i1:k2")
Set test5 = Sheets("Offerte").Range("i4:k6")
Set test6 = Sheets("Offerte").Range("i8:k10")




Sheets("Offerte").Select


PrintRange = Union(test4, test5, test6)


ActiveSheet.PageSetup.PrintArea = PrintRange


' For i = 4 To i = 6
' If Cells(i, 11) = 1 Then
' testadres = "test" & i
' PrintRange = Union(PrintRange, testadres)
' End If
' Next i

End Sub


This is not related to the above test data, but the idea is the same.

Anyway, any input is appreciated, also if it's completely different from this.

I am running Excel for mac 2013

[mdmackillop]

This will create additional pages for each PrintArea and send these to the printer as one document; The pages will then be deleted.

Sub PrintArray()
Dim PrintArray() As Variant
Dim i As Long, j As Long
Dim ws As Worksheet
Dim R As Range
Dim arr

arr = Array("$A$1:$B$2", "$A$4:$B$5", "$A$7:$B$8")
Set R = Sheet1.Cells(1, "K").CurrentRegion
Set ws = Sheet1
ReDim PrintArray(R.Cells.Count)

j = 0
For i = 1 To R.Cells.Count
If R(i) = True Then
ws.Copy after:=Sheets(Sheets.Count)
ActiveSheet.PageSetup.PrintArea = arr(i - 1)
PrintArray(j) = ActiveSheet.Name
j = j + 1
End If
Next i

ReDim Preserve PrintArray(j - 1)
Sheets(PrintArray).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1

Application.DisplayAlerts = False
For i = 0 To UBound(PrintArray)
Sheets(PrintArray(i)).Delete
Next i
Application.DisplayAlerts = True
End Sub

[Raiko]

Hey, Sorry for the late reply, this works perfectly for what i want. I'm currently working on integrating it into the main sheet, and while doing that i realised i would like one more bit of functionality added. As the script takes the list of trues and falses to create the printout, i would like it to read a list of cell values that refer to named ranges, so i can edit the range of the script through namedranges in the worksheet, rather than having to edit the script directly, to make it so that other people can change the references as well. I've gotten almost all of it to work, but i'm running into the problem that a text value in a cell does not convert into a namedrange.




Sub PrintArray()
Dim PrintArray() As Variant
Dim i As Long, j As Long, k As Long, n As Long
Dim ws, returnhome As Worksheet
Dim R, P As Range
Dim arr

Set returnhome = ActiveSheet 'to return to original sheet after printing

'fill the array "arr" with named ranges referring to the pages that can be printed
arr = Array("Page1", "page2", "Page3", "Page4") 'Original code that works perfectly

' Set P = Range("PageNumbers") 'botched attempt that gives type mismatch
' n = P.Cells.Count
'
' ReDim arr(1 To n)
'
' For k = 1 To n
' arr(k) = Range(P(k))
' Next k


Set R = Range("PageSelection") 'refers to named range PageSelection in worksheet
'Set R = ActiveSheet.Cells(1, "K").CurrentRegion
Set ws = Sheet1
ReDim PrintArray(R.Cells.Count)

j = 0
For i = 1 To R.Cells.Count
If R(i) = True Then
ws.Copy after:=Sheets(Sheets.Count)
ActiveSheet.PageSetup.PrintArea = arr(i - 1)
PrintArray(j) = ActiveSheet.Name
j = j + 1
End If
Next i

'i don't really understand what happens here, but it works
ReDim Preserve PrintArray(j - 1)
Sheets(PrintArray).Select
Application.Dialogs(xlDialogPrint).Show 'to show print menu of selection
'ActiveWindow.SelectedSheets.PrintOut Copies:=1 'to print directly

'to remove all worksheets created for the printout
Application.DisplayAlerts = False
For i = 0 To UBound(PrintArray)
Sheets(PrintArray(i)).Delete
Next i
Application.DisplayAlerts = True

returnhome.Select

End Sub


And an example workbook that works with the code
19073

What i'm hoping to achieve there is that i can refer to the PageNumbers named range, and then read each value in the cells as a named range.

[Raiko]

Hmm, turns out i needed to think about it exaclty five minutes longer




Sub PrintArray()
Dim PrintArray() As Variant
Dim i As Long, j As Long, k As Long, n As Long
Dim ws, returnhome As Worksheet
Dim R, P As Range
Dim arr

Set returnhome = ActiveSheet 'to return to original sheet after printing

'fill the array "arr" with named ranges referring to the pages that can be printed
' arr = Array("Page1", "page2", "Page3", "Page4") 'Original code that works perfectly

Set P = Range("PageNumbers") 'NO LONGER botched attempt that gives type mismatch
n = P.Cells.Count


ReDim arr(1 To n)


For k = 1 To n
arr(k) = Array(P(k))
Next k


Set R = Range("PageSelection") 'refers to named range PageSelection in worksheet
'Set R = ActiveSheet.Cells(1, "K").CurrentRegion
Set ws = Sheet1
ReDim PrintArray(R.Cells.Count)

j = 0
For i = 1 To R.Cells.Count
If R(i) = True Then
ws.Copy after:=Sheets(Sheets.Count)
ActiveSheet.PageSetup.PrintArea = arr(i)
PrintArray(j) = ActiveSheet.Name
j = j + 1
End If
Next i

'i don't really understand what happens here, but it works
ReDim Preserve PrintArray(j - 1)
Sheets(PrintArray).Select
Application.Dialogs(xlDialogPrint).Show 'to show print menu of selection
'ActiveWindow.SelectedSheets.PrintOut Copies:=1 'to print directly

'to remove all worksheets created for the printout
Application.DisplayAlerts = False
For i = 0 To UBound(PrintArray)
Sheets(PrintArray(i)).Delete
Next i
Application.DisplayAlerts = True

returnhome.Select

End Sub


19074

[edit] added workbook with updated code

[mdmackillop]

'i don't really understand what happens here, but it works
ReDim Preserve PrintArray(j - 1)
This line creates an array of size to hold all possible pages
ReDim PrintArray(R.Cells.Count)

This section populates the array and increments j on each loop. The final increment is not used in the array.
j = 0
PrintArray(j) = ActiveSheet.Name
j = j + 1


This line resizes the array to the actual numbers of elements entered which will be 1 less than j. "Preserve" means keep the items, otherwise resizing deletes the contents.
ReDim Preserve PrintArray(j - 1)