setting the print area from a macro
 

Hi
Is there any way to set a print area from a macro

setting the print area from a macro
 

try this one

ActiveSheet.PageSetup.PrintArea = "$A$1:$D$10"

do rate

"Joe Farruggio" wrote:

Hi
Is there any way to set a print area from a macro

setting the print area from a macro
 

Yes...record a macro while you set the print area.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Joe Farruggio"

wrote in message
...
Hi
Is there any way to set a print area from a macro

setting the print area from a macro
 

Jim Cone wrote:
Yes...record a macro while you set the print area.
Joe wrote:
I did that,however the functions called require the coordinates in
A1C1 notation. My problem is that my macro uses number coordinates since
the print area will change when different inputs are used for the macro

setting the print area from a macro
 

There is A1 reference style and R1C1 reference style but no A1C1?

Add the address property to your range designation to provide a
string for the print area...

ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(27, 6)).Address
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"Joe Farruggio"
wrote in message
Jim Cone wrote:
Yes...record a macro while you set the print area.

Joe wrote:
I did that,however the functions called require the coordinates in
A1C1 notation. My problem is that my macro uses number coordinates since
the print area will change when different inputs are used for the macro

setting the print area from a macro
 

Jim Cone wrote:
There is A1 reference style and R1C1 reference style but no A1C1?

Add the address property to your range designation to provide a
string for the print area...

ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(27, 6)).Address
Joe Farruggio wrote:
did that and got address but the printarea shown is all wrong. Here is
the macro i used The value of x looks correct when i stop the macro(
toggle a breakpoint)

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/23/2006 by Joe Farruggio
'
Cells(1, 1).Select
'Num = 32,36,40,44 and is selectable
'Numg = 8,9,10,11,12,13 and is selectable
Num = Cells(2, 6).Value
Numg = Cells(3, 3).Value
Range(Cells(1, 1), Cells(9 + Num, 3 + Numg)).Select
x = Range(Cells(1, 1), Cells(9 + Num, 3 + Numg)).Address
ActiveSheet.PageSetup.PrintArea = ActiveSheet.PageSetup.PrintArea = x
'ActiveCell.CurrentRegion.Address
ActiveWindow.SelectedSheets.PrintPreview
'Range("A1").Select
End Sub

Another result is that colummn A and B are collapsed

Thanks for your help

setting the print area from a macro
 

You got a little carried away using the PrintArea.
This worked for me ...
Sub Macro1()
Dim Num As Long
Dim Numg As Long
Dim x As String
Cells(1, 1).Select
'Num = 32,36,40,44 and is selectable
'Numg = 8,9,10,11,12,13 and is selectable
Num = Cells(2, 6).Value
Numg = Cells(3, 3).Value
x = Range(Cells(1, 1), Cells(9 + Num, 3 + Numg)).Address
ActiveSheet.PageSetup.PrintArea = x
ActiveWindow.SelectedSheets.PrintPreview
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Joe Farruggio"
wrote in message
Jim Cone wrote:
There is A1 reference style and R1C1 reference style but no A1C1?

Add the address property to your range designation to provide a
string for the print area...

ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(27, 6)).Address
Joe Farruggio wrote:
did that and got address but the printarea shown is all wrong. Here is
the macro i used The value of x looks correct when i stop the macro(
toggle a breakpoint)

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/23/2006 by Joe Farruggio
'
Cells(1, 1).Select
'Num = 32,36,40,44 and is selectable
'Numg = 8,9,10,11,12,13 and is selectable
Num = Cells(2, 6).Value
Numg = Cells(3, 3).Value
Range(Cells(1, 1), Cells(9 + Num, 3 + Numg)).Select
x = Range(Cells(1, 1), Cells(9 + Num, 3 + Numg)).Address
ActiveSheet.PageSetup.PrintArea = ActiveSheet.PageSetup.PrintArea = x
'ActiveCell.CurrentRegion.Address
ActiveWindow.SelectedSheets.PrintPreview
'Range("A1").Select
End Sub

Another result is that colummn A and B are collapsed

Thanks for your help

setting the print area from a macro
 

Jim Cone wrote:
You got a little carried away using the PrintArea.
This worked for me ...
Sub Macro1()
Dim Num As Long
Dim Numg As Long
Dim x As String
Cells(1, 1).Select
'Num = 32,36,40,44 and is selectable
'Numg = 8,9,10,11,12,13 and is selectable
Num = Cells(2, 6).Value
Numg = Cells(3, 3).Value
x = Range(Cells(1, 1), Cells(9 + Num, 3 + Numg)).Address
ActiveSheet.PageSetup.PrintArea = x
ActiveWindow.SelectedSheets.PrintPreview
End Sub
Joe Farruggio wrote
The problem seems to be that no matter what values i use for
Num and Numg, PrintPreview shows the entire page from A1 to U265

setting the print area from a macro
 

Joe Farruggio wrote:
The problem is solved. The macro now looks like this

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/23/2006 by Joe Farruggio
'
Cells(1, 1).Select
Num = Cells(2, 6).Value
Numg = Cells(3, 3).Value
x = Range(Cells(1, 1), Cells(9 + Num, 3 + Numg)).Address
ActiveSheet.PageSetup.PrintArea = x
ActiveWindow.ActiveSheet.PrintPreview
End Sub

Seems that all the other junk i had inserted during the testing phase
was screwing up the works.

Thanks again for your help- couldn't have done it without you

setting the print area from a macro
 

It works correctly for me.
Try adding a message box to see what address is being used...
'-----------
x = Range(Cells(1, 1), Cells(9 + Num, 3 + Numg)).Address
MsgBox x
ActiveSheet.PageSetup.PrintArea = x
'-----------
Also, the code should go in a standard module, not a sheet module.
Jim Cone


"Joe Farruggio"
wrote in message
The problem seems to be that no matter what values i use for
Num and Numg, PrintPreview shows the entire page from A1 to U265

setting the print area from a macro
 

You are welcome, glad you got it figured out.
Jim Cone
San Francisco, USA


"Joe Farruggio"
wrote in message
Joe Farruggio wrote:
The problem is solved. The macro now looks like this

Sub Macro1()
' Macro1 Macro
' Macro recorded 11/23/2006 by Joe Farruggio

Cells(1, 1).Select
Num = Cells(2, 6).Value
Numg = Cells(3, 3).Value
x = Range(Cells(1, 1), Cells(9 + Num, 3 + Numg)).Address
ActiveSheet.PageSetup.PrintArea = x
ActiveWindow.ActiveSheet.PrintPreview
End Sub

Seems that all the other junk i had inserted during the testing phase
was screwing up the works.
Thanks again for your help- couldn't have done it without you