• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Use VBA to Set Print Area|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Use VBA to Set Print Area|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosUse VBA to Set Print Area
sp_PrintTopic sp_TopicIcon
Use VBA to Set Print Area
Avatar
Mike Devola
Member
Members
Level 0
Forum Posts: 10
Member Since:
August 14, 2021
sp_UserOfflineSmall Offline
1
October 16, 2021 - 3:52 am
sp_Permalink sp_Print

Hello,

I am trying to use a macro to print a number of pages based on the highest value in a range of cells. The values in the range of cells can change based on the quantity that is entered. But due to other criteria, I may need to disregard the highest value (example: say 100 is the max value, but I may not have to record all 100 results on the sheet). I must repeat this for several sheets within the workbook, where the number of sheet may vary as well. I have attached the code I have tried, the first part prints the fixed pages of the workbook, but does not do the rest. thank you in advance for any solutions.

Mike 

sp_AnswersTopicSeeAnswer See Answer
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
October 18, 2021 - 12:55 pm
sp_Permalink sp_Print sp_EditHistory

Hi Mike,

There is no file attached, please use the button Start Upload, not just Select Files.

PrintArea is easy to set, here is an example, you just have to decide the value of the LastRow parameter based on your criteria:

Dim LastRow as long

With Worksheets("Sheet1")

LastRow=.Cells.Find("*",.Cells(1),,,xlByRows, xlPrevious).Row

.PageSetup.PrintArea = "$A$1:$J$" & LastRow

End With

Avatar
Mike Devola
Member
Members
Level 0
Forum Posts: 10
Member Since:
August 14, 2021
sp_UserOfflineSmall Offline
3
October 18, 2021 - 8:24 pm
sp_Permalink sp_Print

Sorry, hope it uploaded this time.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
October 18, 2021 - 9:05 pm
sp_Permalink sp_Print

Hi Mike,

Try to use ElseIf conditions, or select case. In your code, each If line will be tested and that overwrites the previous result, if there are more than 1 lines matching criteria.

Dim PrintAreaAddress As String
If Z < 20 Then
PrintAreaAddress = "$A$1:$AF$65"
ElseIf Z > 20 Or pa <= 40 Then
PrintAreaAddress = "$A$1:$AF$105"
ElseIf Z > 40 Or pa <= 60 Then
PrintAreaAddress = "$A$1:$AF$145"
'....other conditions
End If

Worksheets("Sheet1").PageSetup.PrintArea = PrintAreaAddress

Avatar
Mike Devola
Member
Members
Level 0
Forum Posts: 10
Member Since:
August 14, 2021
sp_UserOfflineSmall Offline
5
October 19, 2021 - 2:53 am
sp_Permalink sp_Print

Hello,

Thank you for your help, unfortunately I am still having a few problems. The ElseIf statement does not return the correct PrintAreaAddress response and the pages do not print. I have attached my spreadsheet for you to see what I am trying to do.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
October 19, 2021 - 11:21 am
sp_Permalink sp_Print

Hi Mike,

You have to clarify the logical chain, previously you was using 2 parameters (Z > 40 Or pa), now you use only "z". In this case, use AND not OR, otherwise for z>20, the only answer will be PrintAreaAddress = "$A$1:$AF$105" (the first condition met):

If z < 20 Then
PrintAreaAddress = "$A$1:$AF$65"
ElseIf z > 20 AND z <= 40 Then
PrintAreaAddress = "$A$1:$AF$105"
ElseIf z > 40 AND z <= 60 Then
PrintAreaAddress = "$A$1:$AF$145"
ElseIf z > 60 AND z <= 80 Then

...

Make sure the line of code that prints the page at the end of your procedure is not commented out, otherwise it will obviously not print anything.

Avatar
Mike Devola
Member
Members
Level 0
Forum Posts: 10
Member Since:
August 14, 2021
sp_UserOfflineSmall Offline
7
October 20, 2021 - 2:23 am
sp_Permalink sp_Print

Hello again,Thank you, you solved the initial issue I was having, now I have another. On my spreadsheet, I have sheets labeled as Inspection Sheet (1), Inspection Sheet (2), ect. My problem is that on each spreadsheet created there can be a varying number on these sheets (There can be zero Inspection sheets up to any amount required for the job). My code works until I get to the point that the next sheet does not exist. At that point an error message appears, so I added the line of code - On Error Resume next - which gets rid of the error message, but repeats the printing of the existing sheets. I created a loop thinking that with If, Then, Else statements it would only print the existing sheets. I have attached my spreadsheet with the code.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
October 20, 2021 - 2:49 am
sp_Permalink sp_Print sp_EditHistory

This code can be replaced:

For I = 1 To 20
On Error Resume Next
If I = 1 And Sheets("Inspection Sheet (1)").Visible = True Then
Worksheets("Inspection Sheet (1)").Activate
ElseIf I = 2 And Sheets("Inspection Sheet (2)").Visible = True Then
Worksheets("Inspection Sheet (2)").Activate
ElseIf I = 3 And Sheets("Inspection Sheet (3)").Visible = True Then
Worksheets("Inspection Sheet (3)").Activate
ElseIf I = 4 And Sheets("Inspection Sheet (4)").Visible = True Then
Worksheets("Inspection Sheet (4)").Activate
ElseIf I = 5 And Sheets("Inspection Sheet (5)").Visible = True Then
Worksheets("Inspection Sheet (5)").Activate
ElseIf I = 6 And Sheets("Inspection Sheet (6)").Visible = True Then
Worksheets("Inspection Sheet (6)").Activate
ElseIf I = 7 And Sheets("Inspection Sheet (7)").Visible = True Then
Worksheets("Inspection Sheet (7)").Activate
ElseIf I = 8 And Sheets("Inspection Sheet (8)").Visible = True Then
Worksheets("Inspection Sheet (8)").Activate
ElseIf I = 9 And Sheets("Inspection Sheet (9)").Visible = True Then
Worksheets("Inspection Sheet (9)").Activate
ElseIf I = 10 And Sheets("Inspection Sheet (10)").Visible = True Then
Worksheets("Inspection Sheet (10)").Activate
ElseIf I = 11 And Sheets("Inspection Sheet (11)").Visible = True Then
Worksheets("Inspection Sheet (11)").Activate
ElseIf I = 12 And Sheets("Inspection Sheet (12)").Visible = True Then
Worksheets("Inspection Sheet (12)").Activate
ElseIf I = 13 And Sheets("Inspection Sheet (13)").Visible = True Then
Worksheets("Inspection Sheet (13)").Activate
ElseIf I = 14 And Sheets("Inspection Sheet (14)").Visible = True Then
Worksheets("Inspection Sheet (14)").Activate
ElseIf I = 15 And Sheets("Inspection Sheet (15)").Visible = True Then
Worksheets("Inspection Sheet (15)").Activate
ElseIf I = 16 And Sheets("Inspection Sheet (16)").Visible = True Then
Worksheets("Inspection Sheet (16)").Activate
ElseIf I = 17 And Sheets("Inspection Sheet (17)").Visible = True Then
Worksheets("Inspection Sheet (17)").Activate
ElseIf I = 18 And Sheets("Inspection Sheet (18)").Visible = True Then
Worksheets("Inspection Sheet (18)").Activate
ElseIf I = 19 And Sheets("Inspection Sheet (19)").Visible = True Then
Worksheets("Inspection Sheet (19)").Activate
ElseIf I = 20 And Sheets("Inspection Sheet (20)").Visible = True Then
Worksheets("Inspection Sheet (20)").Activate
100 End If

Use instead:

For I = 1 To 20
If SheetExists("Inspection Sheet (" & I & ")")=true then

If Thisworkbook.Worksheets("Inspection Sheet (" & I & ")").Visible=True then Thisworkbook.Worksheets("Inspection Sheet (" & I & ")").Activate

Else

Exit For ' stop the loop if a sheet is missing

End if

Next I

 

This function is used in the code above:

Function SheetExists(ByVal ShName As String) As Boolean
On Error Resume Next
SheetExists = ThisWorkbook.Worksheets(ShName).Name <> ""
End Function
sp_AnswersTopicAnswer
Answers Post
Avatar
Mike Devola
Member
Members
Level 0
Forum Posts: 10
Member Since:
August 14, 2021
sp_UserOfflineSmall Offline
9
October 22, 2021 - 5:04 am
sp_Permalink sp_Print

Hello, sorry to be such a bother, but something is not working properly with this code. I have tried numerous scenarios and it keeps failing.

The examples I was creating, the results should have printed 2 pages for the 1st sheet, 1 pages for 2nd sheet, and 2 pages for the 3rd sheet, which worked. But if I make a change where the results should be 1 page for the 1st sheet, 1 page for the 2nd sheet and 2 pages for the 3rd sheet, it only prints 1 page for the 3rd sheet. I also tried where the results should be 2 pages for the 1st, 2 pages for the 2nd, and 1 page for the third, but it printed 2 pages for the 3rd. I looked at the code and it appears to be ok, so not sure what is going on.

If z < 20 Then
PrintAreaAddress = "$A$1:$AF$65"
ElseIf z > 20 AND z <= 40 Then
PrintAreaAddress = "$A$1:$AF$105"
ElseIf z > 40 AND z <= 60 Then
PrintAreaAddress = "$A$1:$AF$145"
ElseIf z > 60 AND z <= 80 Then

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
10
October 22, 2021 - 1:22 pm
sp_Permalink sp_Print

The VBA Project is password protected in all files provided, can't help you.

Avatar
Mike Devola
Member
Members
Level 0
Forum Posts: 10
Member Since:
August 14, 2021
sp_UserOfflineSmall Offline
11
October 22, 2021 - 8:16 pm
sp_Permalink sp_Print

Sorry, I forgot to unprotect before sending.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
12
October 23, 2021 - 2:59 pm
sp_Permalink sp_Print

Hi Mike,

At the begiinning of the PrintSheets procedure, your code is printing visible sheets WITHOUT setting the print area. After printing those, you calculate the print area just for the active sheet:

ActiveSheet.PageSetup.PrintArea

There is no place in code that sets the print area for the sheets you mentioned (first, second, third and so on), keep in mind that print area must be set BEFORE printing for each of the sheets you want to print, does not make any sense to set the print area after printing.

Avatar
Mike Devola
Member
Members
Level 0
Forum Posts: 10
Member Since:
August 14, 2021
sp_UserOfflineSmall Offline
13
October 25, 2021 - 9:39 pm
sp_Permalink sp_Print

Hello,

I tried setting setting the print area for the first set of sheets, it still only printed one page of the inspection sheet, when it should have printed 2.

The first sheets I mention are of predetermined size, I set the print area for those in the Page Layout - Print Area drop down menu. these first sheets are all on 8½ by 11 paper, portrait orientation, 1 page printouts. The Inspection Sheet pages are on 11 x 17 paper, landscape orientation, and can vary in number of pages that need printed. Also, the number of Inspection Sheets can vary, so I only need to change print area conditions on the Inspection Sheets. Everything works till there is any change made that effects the number of pages for each individual Inspection Sheet.  

Avatar
Mike Devola
Member
Members
Level 0
Forum Posts: 10
Member Since:
August 14, 2021
sp_UserOfflineSmall Offline
14
October 26, 2021 - 11:03 pm
sp_Permalink sp_Print

Hello,

 

I want to thank your for all your help and patience,  I found my mistake and have everything working correctly.

Mike

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
15
October 27, 2021 - 1:24 am
sp_Permalink sp_Print

Great, glad to hear you managed to make it work! 🙂

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, Richard West, Wesley Burchnall, Emma Klonowski, Kelly smith
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27291

 

Member Stats:
Guest Posters: 49
Members: 31913
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.