Log In
Register
Home
August 14, 2021
Offline
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
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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:
With Worksheets("Sheet1")
LastRow=.Cells.Find("*",.Cells(1),,,xlByRows, xlPrevious).Row
.PageSetup.PrintArea = "$A$1:$J$" & LastRow
End With
August 14, 2021
Offline
Sorry, hope it uploaded this time.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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
August 14, 2021
Offline
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
August 14, 2021
Offline
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
This code can be replaced:
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:
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:
On Error Resume Next
SheetExists = ThisWorkbook.Worksheets(ShName).Name <> ""
End Function
Answers Post
August 14, 2021
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
The VBA Project is password protected in all files provided, can't help you.
August 14, 2021
Offline
Sorry, I forgot to unprotect before sending.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
August 14, 2021
Offline
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.
August 14, 2021
Offline
Hello,
I want to thank your for all your help and patience, I found my mistake and have everything working correctly.
Mike
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Great, glad to hear you managed to make it work! 🙂
1 Guest(s)
