J
Joe Farruggio
Hi
Is there any way to set a print area from a macro
Is there any way to set a print area from a macro
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
try this one
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$10"
do rate
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$10"
do rate
J
Jim Cone
Yes...record a macro while you set the print area.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
"Joe Farruggio"
<[email protected]>
wrote in message
Hi
Is there any way to set a print area from a macro
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
"Joe Farruggio"
<[email protected]>
wrote in message
Hi
Is there any way to set a print area from a macro
J
Joe Farruggio
Joe wrote:Jim said:Yes...record a macro while you set the print area.
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
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
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.
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
Joe Farruggio wrote: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
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
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
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
Joe Farruggio wrote: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
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
Joe Farruggio wroteJim 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
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
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
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
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
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.