setting the print area from a macro

J

Joe Farruggio

Jim said:
Yes...record a macro while you set the print area.
Click to expand...
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
 
J

Jim Cone

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" <[email protected]>
wrote in message
Jim said:
Yes...record a macro while you set the print area.
Click to expand...

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
 
J

Joe Farruggio

Jim said:
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
Click to expand...
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
 
J

Jim Cone

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" <[email protected]>
wrote in message
Jim said:
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
Click to expand...
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
 
J

Joe Farruggio

Jim said:
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
Click to expand...
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
 
J

Joe Farruggio

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
 
J

Jim Cone

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" <[email protected]>
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
 
J

Jim Cone

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


"Joe Farruggio" <[email protected]>
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
 
Top