Originally Posted by 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 '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

Originally Posted by 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.

Originally Posted by 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