Different Views Printing macro

  • Thread starter Thread starter redroyx
  • Start date Start date
R

redroyx

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
 
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.aspx?scid=kb;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
 
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 Before:=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=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 Before:=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=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 Before:=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Range("cell").Select
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=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 said:
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.aspx?scid=kb;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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Custom print macro 2
Need Macro to Print Selected Wkshts 3
Macro for hiding/unhiding a set of rows 3
Open print dialogue box when click on print icon in tool bar 5
Hide, unhide macro 19
Change macro button text when macro is invoked. 2
Print Macro code help 1
Excel 2007: deleting macro from personal.xlsb 10

Back
Top