variable print area?

[LadyReader]

I have 12 charts on a spreadsheet, but the number can vary. I want t
print each chart on a separate page. Instead of setting up 12 prin
routines, I'd like to set up 1 routine with variable start cell and en
cell settings. The starting row of each chart is offset by 22 rows fro
the one before it.

I have:


Code
-------------------

Public Sub PrintCharts()
Dim StartCol As String
Dim EndCol As String
Dim StartRow As Single
Dim EndRow As Single
Dim Offset as Single

'Initialize:
StartCol = "A"
EndCol = "G"
StartRow = 1
EndRow = 21
Offset = 22

'Navigate to correct spreadsheet:
Sheets("ChartPage").Select

Range(Cells(StartRow, StartCol), Cells(EndRow, EndCol)).Select
ActiveSheet.PageSetup.PrintArea = Selection
...
End Sub

-------------------


The Select line executes but I get an error on the last line of code
where I try to set the PrintArea to my Selection.

The error is: "Unable to set the PrintArea property of the PageSetu
class"

Can anyone explain how I can achieve my goal of using variabl
references?

Thank yo

 

[Guest]

This is completely untested, but...


Sub Macro1()
Dim fc, lc, fr, lr, count As Long
Dim r As Range
fc = 1
fr = 1
lc = 7
lr = 21

For count = 1 To 22
Set r = Range(Cells(fr, fc), Cells(lr, lc))
MsgBox (r.Address)
ActiveSheet.PageSetup.PrintArea = r

' do your printing here

fr = fr + 21
lr = lr + 21
Next
End Sub


Remove the Msgbox() when you are satisfied.

 

[Jim Cone]

Sub PrintAllCharts()
Dim objChtObjects As Excel.ChartObjects
Dim objCht As Excel.ChartObject

Set objChtObjects = ActiveSheet.ChartObjects
For Each objCht In objChtObjects
objCht.Chart.PrintOut
Next
Set objCht = Nothing
Set objChtObjects = Nothing
End Sub
-------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"LadyReader"
<[email protected]>
wrote in message

I have 12 charts on a spreadsheet, but the number can vary. I want to
print each chart on a separate page. Instead of setting up 12 print
routines, I'd like to set up 1 routine with variable start cell and end
cell settings. The starting row of each chart is offset by 22 rows from
the one before it.
I have:
Code:
--------------------

Public Sub PrintCharts()
Dim StartCol As String
Dim EndCol As String
Dim StartRow As Single
Dim EndRow As Single
Dim Offset as Single

'Initialize:
StartCol = "A"
EndCol = "G"
StartRow = 1
EndRow = 21
Offset = 22

'Navigate to correct spreadsheet:
Sheets("ChartPage").Select

Range(Cells(StartRow, StartCol), Cells(EndRow, EndCol)).Select
ActiveSheet.PageSetup.PrintArea = Selection
...
End Sub
--------------------
The Select line executes but I get an error on the last line of code,
where I try to set the PrintArea to my Selection.
The error is: "Unable to set the PrintArea property of the PageSetup
class"
Can anyone explain how I can achieve my goal of using variable
references?
Thank you
LadyReader

 

[LadyReader]

Jim, that's a great answer. Right now I have:


Code:
--------------------
With ActiveSheet.PageSetup
.CenterHeader = "&26Chart 25"
.PrintTitleRows = ""
.PrintTitleColumns = ""
.CenterHorizontally = True
.CenterVertically = True
End With
--------------------


in my code. I'd like to set .CenterHeader dynamically so that the
printed page header is set to the chart title. Can you tell me how I
can address the chart title within your loop?

And the user may want to randomly select which charts to print so I am
working on a UI to allow the selection. Any ideas?

Thanks to all who responded!

 

[Jim Cone]

The worksheet and charts on the worksheet have
independent PageSetUp properties. The modified code
below adds the chart title text to the chart header.
It does not affect the header for the worksheet.
'-----------------------
Sub PrintAllCharts2()
Dim objChtObjects As Excel.ChartObjects
Dim objCht As Excel.ChartObject

Set objChtObjects = ActiveSheet.ChartObjects
For Each objCht In objChtObjects
With objCht.Chart
.PageSetup.CenterHeader = .ChartTitle.Text
.PrintOut
End With
Next
Set objCht = Nothing
Set objChtObjects = Nothing
End Sub
-----------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"LadyReader"
wrote in message
Jim, that's a great answer. Right now I have:
Code:
--------------------
With ActiveSheet.PageSetup
.CenterHeader = "&26Chart 25"
.PrintTitleRows = ""
.PrintTitleColumns = ""
.CenterHorizontally = True
.CenterVertically = True
End With
--------------------

in my code. I'd like to set .CenterHeader dynamically so that the
printed page header is set to the chart title. Can you tell me how I
can address the chart title within your loop?
And the user may want to randomly select which charts to print so I am
working on a UI to allow the selection. Any ideas?
Thanks to all who responded!
LadyReader