MultiSheet Print Preview not including Charts

[Factor3]

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??)

[vba]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
[/vba]
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]

Are any of them chart sheets?

[Factor3]

No, there are no Chart Sheeets. Each Sheet contains BOTH embedded chart objects as well as numerical values.

[Factor3]

I have attached a sample file that illustrates the phenomena that I am referring to....

[mdmackillop]

A workaround. Copy and paste as Pictures in new worksheets
[vba]
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

[/vba]

[Factor3]

Thanks MD, I'm REALLY excited to try this!

I am running the Macro but on the line
[vba]Sheets(a).Range(rng(a - 1)).CopyPicture Appearance:=xlScreen, Format:=xlPicture[/vba]
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]

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]

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