Printing spreadsheets macro

[GarToms]

Hi All,

I currently print forms which i want a macro for. However the table
size in the form varies. Sometimes it is 3 coulmns and 2 rows,
sometimes 7 coluimns and 20 rows (which goes over 2 pages).

Is there a way the macro button can say ' if >7 columns & 15 rows'
print on 2 pages wide.
If '<3 columns' print on 1 page.

Any help here would be great.

Toms


--
GarToms

[Kevin B]

The following macros assumes that the area to print consists of contiguous
rows and columns.

Substitute the starting range and the sheet name to match your starting cell
and sheet name and this should work for you.

Sub PrintPages()

Dim wb As Workbook
Dim ws As Worksheet
Dim r As Range
Dim iRows As Integer
Dim iCols As Integer

Set wb = ActiveWorkbook
Set ws = wb.Sheets("Sheet1")

'Activate sheet 1 and select cell A1
ws.Activate
Range("A1").Select

'Select the current region and assign the
'range name PrintRange to the selection
Selection.CurrentRegion.Select
Selection.Name = "PrintRange"
Set r = Range("PrintRange")

'Get the number of rows and columns in the
'current PrintRange
iRows = r.Rows.Count
iCols = r.Columns.Count

'Set print parameters based upon row & column
'counts
If iCols > 7 And iRows > 15 Then
With ActiveSheet.PageSetup
.FitToPagesWide = 2
.FitToPagesTall = 1
End With
Else
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End If

Set wb = Nothing
Set ws = Nothing
Set r = Nothing

End Sub

--
Kevin Backmann


"GarToms" wrote:

>
> Hi All,
>
> I currently print forms which i want a macro for. However the table
> size in the form varies. Sometimes it is 3 coulmns and 2 rows,
> sometimes 7 coluimns and 20 rows (which goes over 2 pages).
>
> Is there a way the macro button can say ' if >7 columns & 15 rows'
> print on 2 pages wide.
> If '<3 columns' print on 1 page.
>
> Any help here would be great.
>
> Toms
>
>
> --
> GarToms
>

[GarToms]

That is excellent. Thanks a lot. However I am unable to get it to work
as it gets stuck on the if statement.
Also the 'current region' function for the print range does not include
6 of the end columns (unsure as to why, think it may be due to them not
having formulas in). Is there a function that can drag the selection
across 6 columns each time?

Thanks for the help so far.

Kevin B Wrote:
> The following macros assumes that the area to print consists of
> contiguous
> rows and columns.
>
> Substitute the starting range and the sheet name to match your starting
> cell
> and sheet name and this should work for you.
>
> Sub PrintPages()
>
> Dim wb As Workbook
> Dim ws As Worksheet
> Dim r As Range
> Dim iRows As Integer
> Dim iCols As Integer
>
> Set wb = ActiveWorkbook
> Set ws = wb.Sheets("Sheet1")
>
> 'Activate sheet 1 and select cell A1
> ws.Activate
> Range("A1").Select
>
> 'Select the current region and assign the
> 'range name PrintRange to the selection
> Selection.CurrentRegion.Select
> Selection.Name = "PrintRange"
> Set r = Range("PrintRange")
>
> 'Get the number of rows and columns in the
> 'current PrintRange
> iRows = r.Rows.Count
> iCols = r.Columns.Count
>
> 'Set print parameters based upon row & column
> 'counts
> If iCols 7 And iRows 15 Then 'gets stuck here
> With ActiveSheet.PageSetup
> .FitToPagesWide = 2
> .FitToPagesTall = 1
> End With
> Else
> With ActiveSheet.PageSetup
> .FitToPagesWide = 1
> .FitToPagesTall = 1
> End With
> End If
>
> Set wb = Nothing
> Set ws = Nothing
> Set r = Nothing
>
> End Sub
>
> --
> Kevin Backmann
>
>
> "GarToms" wrote:
>
>
> Hi All,
>
> I currently print forms which i want a macro for. However the table
> size in the form varies. Sometimes it is 3 coulmns and 2 rows,
> sometimes 7 coluimns and 20 rows (which goes over 2 pages).
>
> Is there a way the macro button can say ' if 7 columns & 15 rows'
> print on 2 pages wide.
> If '3 columns' print on 1 page.
>
> Any help here would be great.
>
> Toms
>
>
> --
> GarToms
>


--
GarToms

[GarToms]

i have resolved the issue of not picking up all the cells now however i
get an error 400 when i run the code. Does anyone know how i can
resolve this?

GarToms Wrote:
> That is excellent. Thanks a lot. However I am unable to get it to work
> as it gets stuck on the if statement.
> Also the 'current region' function for the print range does not include
> 6 of the end columns (unsure as to why, think it may be due to them not
> having formulas in). Is there a function that can drag the selection
> across 6 columns each time?
>
> Thanks for the help so far.


--
GarToms

[Kevin B]

If this is still an issued, post your code here so it can be reviewed.
--
Kevin Backmann


"GarToms" wrote:

>
> i have resolved the issue of not picking up all the cells now however i
> get an error 400 when i run the code. Does anyone know how i can
> resolve this?
>
> GarToms Wrote:
> > That is excellent. Thanks a lot. However I am unable to get it to work
> > as it gets stuck on the if statement.
> > Also the 'current region' function for the print range does not include
> > 6 of the end columns (unsure as to why, think it may be due to them not
> > having formulas in). Is there a function that can drag the selection
> > across 6 columns each time?
> >
> > Thanks for the help so far.
>
>
> --
> GarToms
>

[GarToms]

I'v worked it out now. Thanks for your help Kevin.


Kevin B Wrote:
> If this is still an issued, post your code here so it can be reviewed.
> --
> Kevin Backmann
>
>
> "GarToms" wrote:
>
>
> i have resolved the issue of not picking up all the cells now however
> i
> get an error 400 when i run the code. Does anyone know how i can
> resolve this?
>
> GarToms Wrote:
> That is excellent. Thanks a lot. However I am unable to get it to
> work
> as it gets stuck on the if statement.
> Also the 'current region' function for the print range does not
> include
> 6 of the end columns (unsure as to why, think it may be due to them
> not
> having formulas in). Is there a function that can drag the
> selection
> across 6 columns each time?
>
> Thanks for the help so far.
>
>
> --
> GarToms
>


--
GarToms