Printing spreadsheets macro

G

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
 
K

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
 
G

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.
 
G

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?
 
Top