Variable print area via a macro

[Andrew]

Hi there,

I need a VBA code I can run via a macro. I have a table of data in Sheet1,
say starting in cell A1 and finihsing in Z250 - its called the Range. I only
want it to print rows that have a certain value in column A. For example, if
A3, A21, A45 all have "incomplete" as their value, then only these 3 rows
would be printed below the set Print Titles. I also need the option to
automatically hide or not print some columns across the page when printing
using this macro, e.g. columns D, F and H. Any help with this would be really
appreciated. Thanks, Andrew.

 

[Héctor Miguel]

hi, Andrew !

I need a VBA code I can run via a macro.
I have a table of data in Sheet1, say starting in cell A1 and finihsing in Z250 - its called the Range.
I only want it to print rows that have a certain value in column A.
For example, if A3, A21, A45 all have "incomplete" as their value
then only these 3 rows would be printed below the set Print Titles.
I also need the option to automatically hide or not print some columns across the page
when printing using this macro, e.g. columns D, F and H.
Any help with this would be really appreciated. Thanks, Andrew.

copy/paste (or type) the following on each type of code module and...
if any doubts (or further information)... would you please comment ?

hth,
hector.

1) in your workbook code module (ThisWorkbook)

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Worksheets("sheet1")
.Range("d1,f1,h1").EntireColumn.Hidden = True
.Range("a1").AutoFilter 1, "incomplete"
End With
Application.OnTime Now, "Restore"
End Sub

2) in a standard code module

Option Private Module
Sub Restore()
With Worksheets("sheet1")
.Range("d1,f1,h1").EntireColumn.Hidden = False
.Range("a1").AutoFilter
End With
End Sub

 

[Andrew]

Hi there and thanks for your quick response, very kind. That does work but I
have a few questions, please;

1) In cell AA1 I have a drop down list with the different option for
printing - i.e incomplete, finished, archived etc - the selected value in
this cell is what it should filter by. I also have another drop down list in
AB1 that depending on the text (i.e partial report or full report) indicates
which columns should be hidden when printing. Partial Report should hides
columns D,E,F and Full report only hides column S,T,V,W. How can these be
intergrated into the code so as I change the option the screen the print
report also changes? How would I have to show the text (column to hide) in
AB1 so that the code accepts it?

2) In the workbook there are 15 sheets total and this functionality needs to
work on all of the sheets - only one sheet is printed at any one time, but
each sheet has the drop down options to choose from etc. My VBA is not very
good, what do I have to change or copy in VBA to make this work as described
on each sheet!

3) On each worksheet I need a title to appear which comes from the text in
cell AC1 - how can this work so that it always appears, regardless of the
filtering? The title is different for each of the worksheets in the file.

Hopefully this is not too confusing, await your thoughts, Thanks again.

 

[Héctor Miguel]

hi, Andrew !

try with the following... (I hope my undertanding was correct). I added some comments (just in case)
I'm not sure if (AB1 range) contents is Full Report / Partial Report (or just Full / Partial) -?-

if any doubts (or further information)... would you please comment ?
hth,
hector.

1) in your workbook code module (ThisWorkbook)

Private Sub Workbook_BeforePrint(Cancel As Boolean)
' make sure there is ONLY ONE sheet selected to print ... '
If ActiveWindow.SelectedSheets.Count > 1 Then _
Cancel = True: MsgBox "Please, select ONE (single) Sheet ONLY !"
' identify which are the columns to hide (& show later) '
Cols2Hide = IIf(LCase(Range("ab1")) = "full", "s1:t1,v1:w1", "d1:f1")
' set the document "Title" as a CenterHeader in Page Setup '
ActiveSheet.PageSetup.CenterHeader = Range("ac1").Text
' hide the columns '
Range(Cols2Hide).EntireColumn.Hidden = True
' filter according AA1 contents '
Range("a1").AutoFilter 1, Range("aa1").Text
' call the restore procedure (when finished) '
Application.OnTime Now, "Restore"
End Sub

2) in a standard code module

Option Private Module
Public Cols2Hide As String
Sub Restore()
Range(Cols2Hide).EntireColumn.Hidden = False
Range("a1").AutoFilter
End Sub

__ OP __

 

[Andrew]

iThanks, I will give that a go now, but hopefully three last questions, please;

1) I was incorrect before re only the 'full' or partial' print options -
there will in fact be 5 print options from cell AB1, each hiding different
columns. How would I expend the formulae to include these?

2) Also, for some of the print options in AB1 I need to also hide certain
header rows - where would this be best to go in the formulae?

3) is there a way for a certain print options in AB1 it can print 'portrait'
rather than 'landscape'? landscape is the default for this workbook...

Look forward to your response, thanks again, Andrew.

 

[Héctor Miguel]

hi, Andrew ! (see inline)

1) I was incorrect before re only the 'full' or partial' print options
- there will in fact be 5 print options from cell AB1, each hiding different columns.
How would I expend the formulae to include these?

1) change this:
Cols2Hide = IIf(LCase(Range("ab1")) = "full", "s1:t1,v1:w1", "d1:f1")

-> to something like this:
Select Case LCase(Range("ab1"))
Case "full": Cols2Hide = "s1:t1,v1:w1"
Case "partial": Cols2Hide = "d1:f1"
' Add more Cases ... '
End Select

2) Also, for some of the print options in AB1 I need to also hide certain header rows
- where would this be best to go in the formulae?

2) using something like the above ?
(I can't get clearly which the cases are and what conditions to apply)
- add another variable in the standard code module (Rows2Hide ?)
- use as reference column "a" and the row-number to build the string
- use .EntireRow.Hidden (instead of .EntireColumn.Hidden)
- don't forget to reverse in the "Restore" procedure

3) is there a way for a certain print options in AB1 it can print 'portrait' rather than 'landscape'?
landscape is the default for this workbook...

3) (again) when "the condition" is meet...
- add another line to apply: -> ActiveSheet.PageSetup.Orientation = xlPortrait
- don't forget to reverse in the "Restore" procedure: -> ActiveSheet.PageSetup.Orientation = xlLandscape

if any doubts (or further information)... would you please comment ?
hth,
hector.

 

[Andrew]

Have to run now but will try suggestions below at same time tommorow and
advise outcome. Thanks again, really apprecaited.

 

[Andrew]

Hi there,

I have totally stuffed it This is what I have, it does not have any
effect on the print area and comes up with a warning 'Compile Error: Select
Case eithout End Select". Can you please check it all out (does my named area
'Range' affect where you have "Range" ?);

_________

IN THIS WORKBOOK I have the following;

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Worksheets("sheet1")

If ActiveWindow.SelectedSheets.Count > 1 Then _
Cancel = True: MsgBox "Please, select ONE (single) Sheet ONLY !"

Select Case LCase(Range("ab1"))
Case "Internal": Cols2Hide = "p1"
Case "External": Cols2Hide = "k1,l1"
Case "feedback": Cols2Hide = "i1"

Select Case LCase(Range("ab1"))
Case "Internal": Rows2Hide = "4"
Case "External": Cols2Hide = "5"
Case "feedback": Cols2Hide = "4,5"

Select Case LCase(Range("ab1"))
Case "Feedback": ActiveSheet.PageSetup.Orientation = xlPortrait

ActiveSheet.PageSetup.CenterHeader = Range("ac1").Text

Range(Cols2Hide).EntireColumn.Hidden = True

Range(Rows2Hide).EntireRow.Hidden = True

Range("a1").AutoFilter 1, Range("aa1").Text

Application.OnTime Now, "Restore"
End Sub

___________

IN MODULE 1 i have the following;

Option Private Module
Sub Restore()
If ActiveWindow.SelectedSheets.Count > 1 Then _
Cancel = True: MsgBox "Please, select ONE (single) Sheet ONLY !"
ActiveSheet.PageSetup.Orientation = xlLandscape
.Range("h1:t1").EntireColumn.Hidden = False
.Range("h1:t1").EntireRow.Hidden = False
.Range("a1").AutoFilter
End With
End Sub

___________

IN MODULE 2 i have the following;

Option Private Module
Public Cols2Hide As String
Sub Restore()
Range(Cols2Hide).EntireColumn.Hidden = False
Range("a1").AutoFilter
End Sub

 

[Héctor Miguel]

hi, Andrew !

I have totally stuffed it This is what I have
it does not have any effect on the print area and comes up with a warning
'Compile Error: Select Case eithout End Select".
Can you please check it all out (does my named area 'Range' affect where you have "Range" ?) ...

according to your comments for this recent post, you need only two codes:
note that the use of LCase(...) vba-function is for not having to take care of text capitalization
I preffer do a lowercase comparisson rather than case-sensitive capitalization (by default in vba)

hth,
hector.

1) in your workbook code module (ThisWorkbook)

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveWindow.SelectedSheets.Count > 1 Then _
Cancel = True: MsgBox "Please, select ONE (single) Sheet ONLY !"
Select Case LCase(Range("ab1"))
Case "internal"
Cols2Hide = "p1"
Rows2Hide = "a4"
Case "external"
Cols2Hide = "k1,l1"
Rows2Hide = "a5"
Case "feedback"
Cols2Hide = "i1"
Rows2Hide = "a4:a5"
End Select
If LCase(Range("ab1")) = "feedback" Then ActiveSheet.PageSetup.Orientation = xlPortrait
ActiveSheet.PageSetup.CenterHeader = Range("ac1").Text
Range(Cols2Hide).EntireColumn.Hidden = True
Range(Rows2Hide).EntireRow.Hidden = True
Range("a1").AutoFilter 1, Range("aa1").Text
Application.OnTime Now, "Restore"
End Sub

2) in a standard code module

Option Private Module
Sub Restore()
ActiveSheet.PageSetup.Orientation = xlLandscape
Range(Cols2Hide).EntireColumn.Hidden = False
Range(Rows2Hide).EntireRow.Hidden = False
Range("a1").AutoFilter
End Sub

 

[Andrew]

Thanks, all good.

hi, Andrew !


according to your comments for this recent post, you need only two codes:
note that the use of LCase(...) vba-function is for not having to take care of text capitalization
I preffer do a lowercase comparisson rather than case-sensitive capitalization (by default in vba)

hth,
hector.

1) in your workbook code module (ThisWorkbook)

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveWindow.SelectedSheets.Count > 1 Then _
Cancel = True: MsgBox "Please, select ONE (single) Sheet ONLY !"
Select Case LCase(Range("ab1"))
Case "internal"
Cols2Hide = "p1"
Rows2Hide = "a4"
Case "external"
Cols2Hide = "k1,l1"
Rows2Hide = "a5"
Case "feedback"
Cols2Hide = "i1"
Rows2Hide = "a4:a5"
End Select
If LCase(Range("ab1")) = "feedback" Then ActiveSheet.PageSetup.Orientation = xlPortrait
ActiveSheet.PageSetup.CenterHeader = Range("ac1").Text
Range(Cols2Hide).EntireColumn.Hidden = True
Range(Rows2Hide).EntireRow.Hidden = True
Range("a1").AutoFilter 1, Range("aa1").Text
Application.OnTime Now, "Restore"
End Sub

2) in a standard code module

Option Private Module
Sub Restore()
ActiveSheet.PageSetup.Orientation = xlLandscape
Range(Cols2Hide).EntireColumn.Hidden = False
Range(Rows2Hide).EntireRow.Hidden = False
Range("a1").AutoFilter
End Sub