Hi,

I am using XL 2002 and I am working with a file with 45 worksheet
(tabs). The file is a forecasting model for 28 different projects
charts + different scenarios to group the projects. I want to set up
"print manager" with a macro that can allow me to print out th
scenarios. So far I know how to a) group sheets and show the prin
dialog box and b) locate a value in a range from the output of
dropdown list box in the VB.

However, I have different views in each of the worksheets. By this
mean that I have grouped columns to hide or unhide information. I wan
the macro to pick the value from the dropdown list box and from suc
value then select to hide or unhide the columns and change the prin
setup befor showing the print dialog box. I am sort of a beginner and
don't know how to start this new part of my macro.

Can anybody hel me

Cheers

--
redroy
-----------------------------------------------------------------------
redroyx's Profile: http://www.excelforum.com/member.php...fo&userid=1452
View this thread: http://www.excelforum.com/showthread.php?threadid=26148

Hi

Have you checked out the Report Manager add-in which is designed to do this
for you ... have a look at the following knowledge base article
http://support.microsoft.com/default...b;en-us;873209
How to install the Excel 2002 Report Manager add-in in Excel 2003

The report manager allows you to nominate sheets in any order from a
workbook, and for each sheet you can nominate a view (from the insert /
custom view menu) and / or a scenario (if required) and you can also print
consecutive page numbers across the whole lot.

Cheers
JulieD


"redroyx" wrote in message
...

Hi,

I am using XL 2002 and I am working with a file with 45 worksheets
(tabs). The file is a forecasting model for 28 different projects +
charts + different scenarios to group the projects. I want to set up a
"print manager" with a macro that can allow me to print out the
scenarios. So far I know how to a) group sheets and show the print
dialog box and b) locate a value in a range from the output of a
dropdown list box in the VB.

However, I have different views in each of the worksheets. By this I
mean that I have grouped columns to hide or unhide information. I want
the macro to pick the value from the dropdown list box and from such
value then select to hide or unhide the columns and change the print
setup befor showing the print dialog box. I am sort of a beginner and I
don't know how to start this new part of my macro.

Can anybody hel me

Cheers!


--
redroyx
------------------------------------------------------------------------
redroyx's Profile:
http://www.excelforum.com/member.php...o&userid=14521
View this thread: http://www.excelforum.com/showthread...hreadid=261480

Thanks JulieD

Unfortunately I do not have privileges to download add-ins at the office.
However I was able to work out the following code today (an embedded macro),
which works for a single worksheet and am happy to share. The most annoying
part was to figure out the outlined levels (every time you group rows or
columns you create a new outline leve). I did neither know that was the way
the were called or that you could actually create code to change the level
showed.

ActiveSheet.Outline.ShowLevels RowLevels:=(variable from 0 to x),
ColumnLevels:=(variable from 0 to y)

Now I have to work out how to extrapolate it to be able to use it with
multiple worksheets. Any ideas anyone?

Here is the code worked out so far for anyone that may find it useful.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 20/09/2004 by RojasR
'
'
If Sheet7.Range("dropdownboxcell") = "2" Then
Sheets("Sheetname").Select
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=3
ActiveSheet.ResetAllPageBreaks
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.PageSetup.PrintArea = "$cell$:$cell$"
Range("cell").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.VPageBreaks.Add Befo=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.VPageBreaks.Add Befo=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.VPageBreaks.Add Befo=ActiveCell
ActiveWindow.View = xlNormalView
With ActiveSheet.PageSetup
.PrintTitleRows = "$row:$row"
.PrintTitleColumns = "$column:$collumn"
.LeftHeader = "&""Arial,Bold""Title"
.CenterHeader = ""
.RightHeader = "&""Arial,Bold""Second Title"
.LeftFooter = "&""Arial,Bold""&Z&F"
.CenterFooter = "&""Arial,Bold""&P"
.RightFooter = "&""Arial,Bold""&D"
.LeftMargin = Application.InchesToPoints(0.354330708661417)
.RightMargin = Application.InchesToPoints(0.354330708661417)
.TopMargin = Application.InchesToPoints(0.590551181102362)
.BottomMargin = Application.InchesToPoints(0.590551181102362)
.HeaderMargin = Application.InchesToPoints(0.31496062992126)
.FooterMargin = Application.InchesToPoints(0.31496062992126)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA3
.FirstPageNumber = xlAutomatic
.Order = xlOverThenDown
.BlackAndWhite = False
.Zoom = 45
.PrintErrors = xlPrintErrorsDisplayed
End With
Application.Dialogs(xlDialogPrint).Show
Else
If Sheet7.Range("dropdownboxcell") = "3" Then
Sheets("Sheetname").Select
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=2
ActiveSheet.ResetAllPageBreaks
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.PageSetup.PrintArea = "$cell$:$cell$"
Range("cell").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.VPageBreaks.Add Befo=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.VPageBreaks.Add Befo=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.VPageBreaks.Add Befo=ActiveCell
ActiveWindow.View = xlNormalView
With ActiveSheet.PageSetup
.PrintTitleRows = "$row:$row"
.PrintTitleColumns = "$column:$column"
.LeftHeader = "&""Arial,Bold""Title"
.CenterHeader = ""
.RightHeader = "&""Arial,Bold""Second Title"
.LeftFooter = "&""Arial,Bold""&Z&F"
.CenterFooter = "&""Arial,Bold""&P"
.RightFooter = "&""Arial,Bold""&D"
.LeftMargin = Application.InchesToPoints(0.354330708661417)
.RightMargin = Application.InchesToPoints(0.354330708661417)
.TopMargin = Application.InchesToPoints(0.590551181102362)
.BottomMargin = Application.InchesToPoints(0.590551181102362)
.HeaderMargin = Application.InchesToPoints(0.31496062992126)
.FooterMargin = Application.InchesToPoints(0.31496062992126)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlOverThenDown
.BlackAndWhite = False
.Zoom = 48
.PrintErrors = xlPrintErrorsDisplayed
End With
Application.Dialogs(xlDialogPrint).Show
Else
If Sheet7.Range("dropdownboxcell") = "4" Then
Sheets("Sheetname").Select
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
ActiveSheet.ResetAllPageBreaks
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.PageSetup.PrintArea = "$cell$:$cell$"
Range("cell").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.VPageBreaks.Add Befo=ActiveCell
ActiveWindow.View = xlNormalView
With ActiveSheet.PageSetup
.PrintTitleRows = "$row:$row"
.PrintTitleColumns = "$column:$column"
.LeftHeader = "&""Arial,Bold""Title"
.CenterHeader = ""
.RightHeader = "&""Arial,Bold""Second Title"
.LeftFooter = "&""Arial,Bold""&Z&F"
.CenterFooter = "&""Arial,Bold""&P"
.RightFooter = "&""Arial,Bold""&D"
.LeftMargin = Application.InchesToPoints(0.354330708661417)
.RightMargin = Application.InchesToPoints(0.354330708661417)
.TopMargin = Application.InchesToPoints(0.590551181102362)
.BottomMargin = Application.InchesToPoints(0.590551181102362)
.HeaderMargin = Application.InchesToPoints(0.31496062992126)
.FooterMargin = Application.InchesToPoints(0.31496062992126)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlOverThenDown
.BlackAndWhite = False
.Zoom = 49
.PrintErrors = xlPrintErrorsDisplayed
End With
Application.Dialogs(xlDialogPrint).Show
End If
End If
End If
End Sub

Cheers!

redroyx

"JulieD" wrote:

Hi

Have you checked out the Report Manager add-in which is designed to do this
for you ... have a look at the following knowledge base article
http://support.microsoft.com/default...b;en-us;873209
How to install the Excel 2002 Report Manager add-in in Excel 2003

The report manager allows you to nominate sheets in any order from a
workbook, and for each sheet you can nominate a view (from the insert /
custom view menu) and / or a scenario (if required) and you can also print
consecutive page numbers across the whole lot.

Cheers
JulieD


"redroyx" wrote in message
...

Hi,

I am using XL 2002 and I am working with a file with 45 worksheets
(tabs). The file is a forecasting model for 28 different projects +
charts + different scenarios to group the projects. I want to set up a
"print manager" with a macro that can allow me to print out the
scenarios. So far I know how to a) group sheets and show the print
dialog box and b) locate a value in a range from the output of a
dropdown list box in the VB.

However, I have different views in each of the worksheets. By this I
mean that I have grouped columns to hide or unhide information. I want
the macro to pick the value from the dropdown list box and from such
value then select to hide or unhide the columns and change the print
setup befor showing the print dialog box. I am sort of a beginner and I
don't know how to start this new part of my macro.

Can anybody hel me

Cheers!


--
redroyx
------------------------------------------------------------------------
redroyx's Profile:
http://www.excelforum.com/member.php...o&userid=14521
View this thread: http://www.excelforum.com/showthread...hreadid=261480