About Us

Andrew

Thanks, all good.

"HÃ©ctor Miguel" wrote:

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Using named range to extend print area for variable number of columns	Pierre	Excel Worksheet Functions	3	April 10th 08 05:51 PM
How do you set up a macro to reset the print area?	Stuck2	Excel Discussion (Misc queries)	0	January 30th 06 03:50 PM
Pivot Table macro to set print area and print details of drill down data	Steve Haskins	Excel Discussion (Misc queries)	2	December 28th 05 04:59 PM
Create a print macro that would automatically select print area?	wastedwings	Excel Worksheet Functions	7	August 22nd 05 10:36 PM
Variable print area	BOBF	Excel Discussion (Misc queries)	2	March 22nd 05 01:47 PM

Menu

About Us