Printing Suggestions

[alwaysXcel]

Hello All,

I am dealing with a worksheet that has 8 different output tabs. In all these tabs, several columns are hidden based on user input, for example if the user enters 3 as # of companies, 9 columns would be visible and everything else would be hidden. However, when the users try to print, they endup printing 20+ pages becuase the default are for the maximum companies.

How can I effectively set the print ranges so that they would change based on user input?

I was thinking about using Case for # of Companies and pre-record all macros for all the scenarios and then based on the number of companies selected, it would run the macro? But the more and more I look at it, its looking more tedious. Is there any other method which I can use to achieve this?

Thank you!!

[Anne Troy]

Hi! I think you probably didn't get a response because your question is just...a bit overwhelming! :)

Why don't you take your workbook and save it to a new name. Delete all but one or two worksheets. Change the names to protect the innocent...you get the idea? Make a sample file. Put textboxes on the worksheets to explain what you want to do with each (either), and upload it?

See...nobody really wants to spend the time creating a sample file for you, just to find out they didn't understand in the first place.

Good luck!

[alwaysXcel]

Thanks dreamboat, sorry for the confusion.


Here is the attached worksheet. I hope this helps.

[Howard Kaikow]

Hello All,

I am dealing with a worksheet that has 8 different output tabs. In all these tabs, several columns are hidden based on user input, for example if the user enters 3 as # of companies, 9 columns would be visible and everything else would be hidden. However, when the users try to print, they endup printing 20+ pages becuase the default are for the maximum companies.

How can I effectively set the print ranges so that they would change based on user input?

I was thinking about using Case for # of Companies and pre-record all macros for all the scenarios and then based on the number of companies selected, it would run the macro? But the more and more I look at it, its looking more tedious. Is there any other method which I can use to achieve this?

Thank you!!

Have you tried using the WorkBook_BeforePrint event?

I've not tried this, but I expect you could change the print ranges in that event.

[alwaysXcel]

No I have not tried using the beforeprint event. However, The example I provided is only 1 tab, I have 8 tabs that are pretty much identical in content but have different ranges. How would I go about using beforeprint in all of them?

[Howard Kaikow]

No I have not tried using the beforeprint event. However, The example I provided is only 1 tab, I have 8 tabs that are pretty much identical in content but have different ranges. How would I go about using beforeprint in all of them?

BeforePrint is an event that occurs before printing.

I expect that you could set print ranges with the event.

For example the code below is a BeforePrint event I use.



Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveWorkbook
If Not .Saved Then
.Save
End If
SetHeader ActiveSheet
.Saved = True
End With
End Sub


All hte code does is call a sub that sets up the header the way I wish.

I pass the ActiveSheet to the sub.
I could just as well pass other sheets, or do most anything I wish.

As long as the BeforePrint event does not prevent you from doing so, I expect that you could set up the print ranges prior to the actual printing.

Hmmm, indeed, I just remenbered that I actually do that in another workbook.

You should be able to tweak the following.



Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim LastSchwinnCell As Excel.Range
Dim LastWeightCell As Excel.Range
With ActiveSheet
With .Columns(2)
Set LastWeightCell = .Parent.Cells(Rows.Count, .Column).End(xlUp)
End With
.Range("G12").Value = "Weight lost since " & Format(.Range("A2"), "dd mmm yyyy") & ": " _
& .Range("B2") - LastWeightCell.Value & " pounds"
With .Columns(3)
Set LastSchwinnCell = .Parent.Cells(Rows.Count, .Column).End(xlUp)
End With
If LastSchwinnCell.Row > LastWeightCell.Row Then
.PageSetup.PrintArea = "$A$1:$G$" & LastSchwinnCell.Row
Else
.PageSetup.PrintArea = "$A$1:$G$" & LastWeightCell.Row
End If
End With
End Sub

[mdmackillop]

Depending upon the complexity, I would consider hiding the unwanted columns e.g.


Sub SetPrint()
Dim Co5
ActiveSheet.PageSetup.PrintArea = "$A:$Z"
With ActiveSheet
Select Case Range("A3")
Case Is = 5
Co5 = Array("23", "24", "25")
For Each col In Co5
.Columns(col * 1).ColumnWidth = 0
Next
End Select
End With
End Sub

[alwaysXcel]

Thank you for all your help. Based on the feedback provided, I was able to come up with the following:

First of all, I created an identifier on top of each column heading which basically shows if the column heading has a value, it shows it as "used",if it doesnt, it shows as "unused", and then I got the address of the first column that shows unused and I basically created the print range.

Is there a more effective way to achieve this? How can I change the scroll area of the page to yy??

thank you!


Sub printRange()
Dim xx As String
Sheet11.Activate
Range("s3").EntireRow.Select
Do
Cells.Find(What:="unused", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Loop Until ActiveCell.Value = "unused" And ActiveCell.Offset(0, -1).Value = "unused" _
And ActiveCell.Offset(0, 2).Value = "unused"
xx = Left(ActiveCell.Offset(0, -1).Address, 4)
MsgBox xx
With ActiveSheet
With .Columns(3)
Set lastcell = .Parent.Cells(Rows.Count, .Column).End(xlUp)
End With
Dim yy As String
yy = "$A$1:" & "$" & xx & lastcell.Row
.PageSetup.PrintArea = "$A$1:" & xx & lastcell
End With
End Sub

[Anne Troy]

Don't forget vba tags, always. :)

Your code is so much prettier now. LOL

[mdmackillop]

Can you post your revised workbook? It makes life simpler.

[alwaysXcel]

Here is the workbook with the code.

Thanks

[mdmackillop]

I don't see the need for 3 x unused. How about


Sub printRange()
lastcol = Cells.Find(What:="unused", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Column() - 2
lastrow = Range("C65536").End(xlUp).Row()
If lastrow < 40 Then lastrow = 40
ActiveSheet.PageSetup.PrintArea = "$A$1:" & Cells(lastrow, lastcol).Address
Application.Goto Reference:="Print_Area"
End Sub

[alwaysXcel]

Thank you for your response!! I added this line to the current code:


ActiveSheet.ScrollArea = "$A$1:" & Cells(LastRow, LastCol).Address

It works fine, however, when I save the worksheet and reopen it, the scroll area goes away.

[mdmackillop]

Add this to the ThisWorkbook module



Private Sub Workbook_Open()
Sheets("sheet1").Select
printRange
End Sub