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!

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!

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!

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!

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!