PDA

View Full Version : MultiSheet Print Preview not including Charts



Factor3
03-13-2009, 08:24 AM
Hello,

I have the output for a report on 5 different sheets, each sheet contains Charts and numerical output. I want to create a Print Preview screen that includes the Charts and Numerical Output.

The following code DOES A GREAT JOB, but the Charts are missing from the 2nd to 5th Sheet (the Chart on the FIRST SHEET DOES show up??)

Sub PrintMultipleSheets()

With ActiveWorkbook
.Sheets("1").Select False
.Sheets("4").Select False
.Sheets("2").Select False
.Sheets("3").Select False
.Sheets("5").Select False
End With
ActiveWindow.SelectedSheets.PrintPreview

End Sub

NOTE: I WOULD LIKE TO BE ABLE TO DO THIS WITHOUT HAVING TO RE-CODE MY CHARTS (I.E. FIND OUT THEIR EXACT NAMES IN THE PROGRAM ETC.), HOWEVER, I KNOW THE EXACT NUMBER OF CHARTS PER SHEET AND THAT WOULD BE VERY EASY TO CODE IN.

lucas
03-13-2009, 01:49 PM
Are any of them chart sheets?

Factor3
03-13-2009, 08:17 PM
No, there are no Chart Sheeets. Each Sheet contains BOTH embedded chart objects as well as numerical values.

Factor3
03-13-2009, 08:53 PM
I have attached a sample file that illustrates the phenomena that I am referring to....

mdmackillop
03-14-2009, 06:54 AM
A workaround. Copy and paste as Pictures in new worksheets

Sub TestPrint()

arr = Array("1", "4", "2", "3", "5")
Rng = Array("A1:I34", "A1:K36", "A1:N35", "A1:K36", "A1:K36")
For Each a In arr
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = a & "A"
Sheets(a).Range(Rng(a - 1)).CopyPicture Appearance:=xlScreen, Format:=xlPicture
Sheets(a & "A").Pictures.Paste
Sheets(a & "A").PageSetup.Orientation = xlLandscape
Next

PrintMultipleSheets

End Sub

Sub PrintMultipleSheets()

With ActiveWorkbook
.Sheets("1A").Select False
.Sheets("4A").Select False
.Sheets("2A").Select False
.Sheets("3A").Select False
.Sheets("5A").Select False
End With
ActiveWindow.SelectedSheets.PrintPreview

End Sub

Sub DelSheets()
Sheets(Array("1A", "4A", "2A", "3A", "5A")).Select
ActiveWindow.SelectedSheets.Delete
End Sub

Factor3
03-14-2009, 12:37 PM
Thanks MD, I'm REALLY excited to try this!

I am running the Macro but on the line
Sheets(a).Range(rng(a - 1)).CopyPicture Appearance:=xlScreen, Format:=xlPicture
I get a "Run-time error '13':"
Type mismatch

When I scroll over the line a small box pops up that says:
rng(a-1) = <Type Mismatch>

Any idea what I need to do differently to run the Macro all the way through?

Factor3
03-14-2009, 12:43 PM
Sorry MD, NEVERMIND! I added a couple of sheets, and those sheets had string values as names instead of integers, so when I ran the Macro on all of the sheets (which has both string and integer names) the Macro DIDN'T work, but when I ran the Macro on the sheets that ONLY had integer names, I didn't get the type mismatch, so I am just going to rename the sheets ALL integer names.

THANK YOU MD, you are GOD among men!!!!!! You always have such incredible creativity with these things, I can't tell you how much I appreciate your help!!!!!

All the Best,

Benjamin

mdmackillop
03-14-2009, 12:57 PM
Happy to help.
Just a thought, but names like 1, 2, 3 are maybe not the best when coding gets involved. Too easy to make a slip between Sheets("1") and Sheets(1) which in your case are two different sheets.
Regards
MD