Select and print defined areas of spreadsheets

Topic

New Reply

Hi

I am wanting print selected ares of specified worksheets in a workbook.

I came across this code which allows me to select the worksheets to print but does not allow me to specify the area to print.

Option Explicit

Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Application.ScreenUpdating = False

‘ Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox “Workbook is protected.”, vbCritical
Exit Sub
End If

‘ Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

SheetCount = 0

‘ Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
‘ Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) 0 And _
CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i

‘ Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240

‘ Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos – 34)
.Width = 230
.Caption = “Select sheets to print”
End With

‘ Change tab order of OK and Cancel buttons
‘ so the 1st option button will have the focus
PrintDlg.Buttons(“Button 2”).BringToFront
PrintDlg.Buttons(“Button 3”).BringToFront

‘ Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Select Replace:=False
End If
Next cb
ActiveWindow.SelectedSheets.PrintOut copies:=1
ActiveSheet.Select
End If
Else
MsgBox “All worksheets are empty.”
End If

‘ Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete

‘ Reactivate original sheet
CurrentSheet.Activate
End Sub

Is there a way to call up the areas specified in “View|Custom Views”, set the print area based on the view for each worksheet? I am working with 9 worksheets and 1 worksheet has 14 “Custom View” areas defined.

Reply | Quote

Replies

If each view has a defined print area (the print area and sheet are both saved with the view), you can just loop through each view and print the activesheet:

Code:

Sub PrintViews()
Dim cv As CustomView
For Each cv In ActiveWorkbook.CustomViews
cv.Show
ActiveSheet.PrintOut
Next
End Sub

Steve

Reply | Quote

Steve thanks for that

Firstly, I am having trouble getting the Custom Views to stick. Page layout keeps reverting to Automatic for page width and height. I don’t want to print over multiple pages!

On the page where I have 14 CustomViews set I get 14 copies of 1 of the pages. However, if I comment out the ActiveSheet.PrintOut line the code cycles through all the required CustomViews.

Reply | Quote

Could you attach an example file demonstrating the problem? When I set the custom view the print settings are stored with the view and that includes the page dimensions.

Steve

Reply | Quote

Steve
I have managed to figure out how to strip all the sensitive info out, thus
decreasing to size to 500Mb rather than 11.5Mb
This file is still quite
sensitive

Reply | Quote

Steve hi
Did you have any joy with my little problem?

I have managed to determine what the problem was. The main problem is that I set the page layout for each page but this is ot sticking and the layout reverts to a specific , B441:P485, and prints this for all custom views!

Not really what I want.

How can I get the page layout settings to stick for each custom view?

Reply | Quote

You should be able to save the page layout settings in a custom view.

Steve

Reply | Quote