Set print area on variable range
 

I have a macro that creates a sheet for me based upon information that is
input into another sheet. The number of columns on the sheets is constant
A:K, however, the number of rows varies - the max number of rows it can have
is 46. I have searched through the posts but have been unable to find code
that will work that will:
- Scale the page to fit to one page
- Set the print area based on the last used row in column K so that A1:K? is
the print area

As a note: the sheet NAME is generated as part of the macro that creates the
sheet - the name is always unique. That part alone discluded a couple of the
macros that I found in the archives here.

Sorry to post such a seemingly redundant question, but as I said, nothing
that I found seems to do the trick for me.

Thanks in advance!

Set print area on variable range
 

Tom,

Maybe this will give you an idea. The subroutine below will take a
worksheet object as a parameter, set the print range and fit to 1 page.

Sub PrepareSheet(sht As Worksheet)
sht.PageSetup.PrintArea = sht.UsedRange.Address
With sht.PageSetup
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End Sub


To test, you just call the sub routine and pass a worksheet object. for
example

Sub Test
PrepareSheet Worksheets("Sheet1")
End Sub


--
Hope that helps.

Vergel Adriano


"Tom" wrote:

I have a macro that creates a sheet for me based upon information that is
input into another sheet. The number of columns on the sheets is constant
A:K, however, the number of rows varies - the max number of rows it can have
is 46. I have searched through the posts but have been unable to find code
that will work that will:
- Scale the page to fit to one page
- Set the print area based on the last used row in column K so that A1:K? is
the print area

As a note: the sheet NAME is generated as part of the macro that creates the
sheet - the name is always unique. That part alone discluded a couple of the
macros that I found in the archives here.

Sorry to post such a seemingly redundant question, but as I said, nothing
that I found seems to do the trick for me.

Thanks in advance!

Set print area on variable range
 

Hi Vergel,
Thanks for the reply. I guess i should have mentioned that while I have data
that goes beyond column K, I don't want that included in the print range. Is
it possible for it to look at the last used cell in column K and work from
there backwards to A1? I'm thinking that that would be the best way to attack
this???

"Vergel Adriano" wrote:

Tom,

Maybe this will give you an idea. The subroutine below will take a
worksheet object as a parameter, set the print range and fit to 1 page.

Sub PrepareSheet(sht As Worksheet)
sht.PageSetup.PrintArea = sht.UsedRange.Address
With sht.PageSetup
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End Sub


To test, you just call the sub routine and pass a worksheet object. for
example

Sub Test
PrepareSheet Worksheets("Sheet1")
End Sub


--
Hope that helps.

Vergel Adriano


"Tom" wrote:

I have a macro that creates a sheet for me based upon information that is
input into another sheet. The number of columns on the sheets is constant
A:K, however, the number of rows varies - the max number of rows it can have
is 46. I have searched through the posts but have been unable to find code
that will work that will:
- Scale the page to fit to one page
- Set the print area based on the last used row in column K so that A1:K? is
the print area

As a note: the sheet NAME is generated as part of the macro that creates the
sheet - the name is always unique. That part alone discluded a couple of the
macros that I found in the archives here.

Sorry to post such a seemingly redundant question, but as I said, nothing
that I found seems to do the trick for me.

Thanks in advance!

Set print area on variable range
 

Hi Tom,

Try this then

Sub PrepareSheet(sht As Worksheet)
Dim lRow As Long
lRow = sht.Range("A:K").SpecialCells(xlCellTypeLastCell). Row
With sht.PageSetup
.PrintArea = "$A$1:$K$" & lRow
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End Sub



--
Hope that helps.

Vergel Adriano


"Tom" wrote:

Hi Vergel,
Thanks for the reply. I guess i should have mentioned that while I have data
that goes beyond column K, I don't want that included in the print range. Is
it possible for it to look at the last used cell in column K and work from
there backwards to A1? I'm thinking that that would be the best way to attack
this???

"Vergel Adriano" wrote:

Tom,

Maybe this will give you an idea. The subroutine below will take a
worksheet object as a parameter, set the print range and fit to 1 page.

Sub PrepareSheet(sht As Worksheet)
sht.PageSetup.PrintArea = sht.UsedRange.Address
With sht.PageSetup
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End Sub


To test, you just call the sub routine and pass a worksheet object. for
example

Sub Test
PrepareSheet Worksheets("Sheet1")
End Sub


--
Hope that helps.

Vergel Adriano


"Tom" wrote:

I have a macro that creates a sheet for me based upon information that is
input into another sheet. The number of columns on the sheets is constant
A:K, however, the number of rows varies - the max number of rows it can have
is 46. I have searched through the posts but have been unable to find code
that will work that will:
- Scale the page to fit to one page
- Set the print area based on the last used row in column K so that A1:K? is
the print area

As a note: the sheet NAME is generated as part of the macro that creates the
sheet - the name is always unique. That part alone discluded a couple of the
macros that I found in the archives here.

Sorry to post such a seemingly redundant question, but as I said, nothing
that I found seems to do the trick for me.

Thanks in advance!

Set print area on variable range
 

Thanks Vergel, very much appreciated!

"Vergel Adriano" wrote:

Hi Tom,

Try this then

Sub PrepareSheet(sht As Worksheet)
Dim lRow As Long
lRow = sht.Range("A:K").SpecialCells(xlCellTypeLastCell). Row
With sht.PageSetup
.PrintArea = "$A$1:$K$" & lRow
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End Sub



--
Hope that helps.

Vergel Adriano


"Tom" wrote:

Hi Vergel,
Thanks for the reply. I guess i should have mentioned that while I have data
that goes beyond column K, I don't want that included in the print range. Is
it possible for it to look at the last used cell in column K and work from
there backwards to A1? I'm thinking that that would be the best way to attack
this???

"Vergel Adriano" wrote:

Tom,

Maybe this will give you an idea. The subroutine below will take a
worksheet object as a parameter, set the print range and fit to 1 page.

Sub PrepareSheet(sht As Worksheet)
sht.PageSetup.PrintArea = sht.UsedRange.Address
With sht.PageSetup
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End Sub


To test, you just call the sub routine and pass a worksheet object. for
example

Sub Test
PrepareSheet Worksheets("Sheet1")
End Sub


--
Hope that helps.

Vergel Adriano


"Tom" wrote:

I have a macro that creates a sheet for me based upon information that is
input into another sheet. The number of columns on the sheets is constant
A:K, however, the number of rows varies - the max number of rows it can have
is 46. I have searched through the posts but have been unable to find code
that will work that will:
- Scale the page to fit to one page
- Set the print area based on the last used row in column K so that A1:K? is
the print area

As a note: the sheet NAME is generated as part of the macro that creates the
sheet - the name is always unique. That part alone discluded a couple of the
macros that I found in the archives here.

Sorry to post such a seemingly redundant question, but as I said, nothing
that I found seems to do the trick for me.

Thanks in advance!