I am trying to create a report page similar to what Acess does, however I do
not know how to use Acess, so I want to do it in Excel. My vision is to
programmaticlly take all my rows of data and paste it onto a report, using a
userform selection as my filter criteria and here is where the problem lies,
the report can only hold 14 rows of data on a single page, I would like to
know how if possible can I accomplish this task? Here is the code I currently
have which will show how I want the report to look..

Public Sub FormatHeaders()
'format row and columns
Rows("1:1").RowHeight = 45
Rows("2:2").RowHeight = 15.75
Rows("3:3").RowHeight = 21.75
Rows("4:4").RowHeight = 13
Rows("5:21").RowHeight = 33
Columns("A:A").ColumnWidth = 6.57
Columns("E:F").ColumnWidth = 8.43
Columns("G:G").ColumnWidth = 15
Columns("H:H").ColumnWidth = 2.96
Columns("L:M").ColumnWidth = 6.14
Columns("N:N").ColumnWidth = 6.29
Range("C:C,O:O,P:P").ColumnWidth = 6.86
Columns("Q:Q").ColumnWidth = 8.71
Range("B:B,D,I:K").ColumnWidth = 6

'formats row 1 for heading
With Range("A1:Q1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 36
.Font.Bold = True
Range("A1").FormulaR1C1 = "Hazardous Material Inventory"
End With
'formats row 2
With Range("A2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Font.Name = "Arial"
.Font.Size = 12
.Font.Bold = True
Range("A2").FormulaR1C1 = "Unit:"
End With
With Range("B2:E2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Merge
.Font.Name = "Arial"
.Font.Size = 12
End With
With Range("A2:E2")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
End With
With Range("F2:G2")
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 12
.Font.Bold = True
Range("F2").FormulaR1C1 = "Department/Division:"
End With
With Range("H2:L2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
With Range("M2")
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Font.Name = "Arial"
.Font.Size = 12
.Font.Bold = True
Range("M2").FormulaR1C1 = "Date:"
End With
With Range("N2:Q2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
With Range("A3:A4,B3:E4,K3:N3")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 9
.Font.Bold = True
Range("A3").FormulaR1C1 = "MSDS#"
Range("B3").FormulaR1C1 = "Product Name"
Range("K3").FormulaR1C1 = "NFPA/HMIS Rating"
End With
With Range("F3:G4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.WrapText = True
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("F3").FormulaR1C1 = "Manufacturers Name Phone Number"
End With
With Range("H3:H4,I3:I4,J3:J4,O3:O4,P3:P4,Q3:Q4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.WrapText = True
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("H3").FormulaR1C1 = "A / I"
Range("I3").FormulaR1C1 = "EHS (302) YES/NO"
Range("J3").FormulaR1C1 = "Toxic (313) YES/NO"
Range("O3").FormulaR1C1 = "Disposal Code R/Y/G"
Range("P3").FormulaR1C1 = "Quantity on Hand"
Range("Q3").FormulaR1C1 = "Date of Inventory"
End With
With Range("K4,L4,M4,N4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("K4").FormulaR1C1 = "Fire"
Range("L4").FormulaR1C1 = "Health"
Range("M4").FormulaR1C1 = "React"
Range("N4").FormulaR1C1 = "Specific"
End With
Call formatrows
End Sub
Sub formatrows()
With Range("A5:A71,H5:H17")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
End With
With
Range("B5:E5,B6:E6,B7:E7,B8:E8,B9:E9,B10:E10,B11:E11,B12:E12,B13:E13,B14:E14,B15:E15,B16:E16,B17:E17")
.Font.Name = "Arial"
.Font.Size = 9
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
With
Range("F5:G5,F6:G6,F7:G7,F8:G8,F9:G9,F10:G10,F11:G11,F12:G12,F13:G13,F14:G14,F15:G15,F16:G16,F17:G17")
.Font.Name = "Arial"
.Font.Size = 8
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
With Range("I5:J17")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.NumberFormat = """Yes"";""Yes"";""No"""
End With
With Range("K5:M17,N5:O17,P5:P17")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.NumberFormat = "0"
End With
With Range("Q5:Q17")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.NumberFormat = "[$-409]d-mmm-yy;@"
End With
End Sub

Your question is a little thin on details. Are you wanting to know how to
validate that the criteria selected results in a data set of 14 or less items
or did you want to know how to expand your report to allow for more than 14
items???

As an aside you can replace all of this code
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
with
.Borders.LineStyle = xlContinuous

--
HTH...

Jim Thomlinson


"Mekinnik" wrote:

> I am trying to create a report page similar to what Acess does, however I do
> not know how to use Acess, so I want to do it in Excel. My vision is to
> programmaticlly take all my rows of data and paste it onto a report, using a
> userform selection as my filter criteria and here is where the problem lies,
> the report can only hold 14 rows of data on a single page, I would like to
> know how if possible can I accomplish this task? Here is the code I currently
> have which will show how I want the report to look..
>
> Public Sub FormatHeaders()
> 'format row and columns
> Rows("1:1").RowHeight = 45
> Rows("2:2").RowHeight = 15.75
> Rows("3:3").RowHeight = 21.75
> Rows("4:4").RowHeight = 13
> Rows("5:21").RowHeight = 33
> Columns("A:A").ColumnWidth = 6.57
> Columns("E:F").ColumnWidth = 8.43
> Columns("G:G").ColumnWidth = 15
> Columns("H:H").ColumnWidth = 2.96
> Columns("L:M").ColumnWidth = 6.14
> Columns("N:N").ColumnWidth = 6.29
> Range("C:C,O:O,P:P").ColumnWidth = 6.86
> Columns("Q:Q").ColumnWidth = 8.71
> Range("B:B,D,I:K").ColumnWidth = 6
>
> 'formats row 1 for heading
> With Range("A1:Q1")
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlBottom
> .Merge
> .Borders(xlDiagonalDown).LineStyle = xlNone
> .Borders(xlDiagonalUp).LineStyle = xlNone
> .Borders(xlEdgeTop).LineStyle = xlContinuous
> .Borders(xlEdgeBottom).LineStyle = xlContinuous
> .Borders(xlEdgeRight).LineStyle = xlContinuous
> .Borders(xlEdgeLeft).LineStyle = xlContinuous
> .Font.Name = "Arial"
> .Font.Size = 36
> .Font.Bold = True
> Range("A1").FormulaR1C1 = "Hazardous Material Inventory"
> End With
> 'formats row 2
> With Range("A2")
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlBottom
> .Font.Name = "Arial"
> .Font.Size = 12
> .Font.Bold = True
> Range("A2").FormulaR1C1 = "Unit:"
> End With
> With Range("B2:E2")
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlBottom
> .Merge
> .Font.Name = "Arial"
> .Font.Size = 12
> End With
> With Range("A2:E2")
> .Borders(xlDiagonalDown).LineStyle = xlNone
> .Borders(xlDiagonalUp).LineStyle = xlNone
> .Borders(xlEdgeTop).LineStyle = xlContinuous
> .Borders(xlEdgeBottom).LineStyle = xlContinuous
> .Borders(xlEdgeRight).LineStyle = xlContinuous
> .Borders(xlEdgeLeft).LineStyle = xlContinuous
> End With
> With Range("F2:G2")
> .HorizontalAlignment = xlLeft
> .VerticalAlignment = xlBottom
> .Borders(xlDiagonalDown).LineStyle = xlNone
> .Borders(xlDiagonalUp).LineStyle = xlNone
> .Borders(xlEdgeTop).LineStyle = xlContinuous
> .Borders(xlEdgeBottom).LineStyle = xlContinuous
> .Borders(xlEdgeLeft).LineStyle = xlContinuous
> .Font.Name = "Arial"
> .Font.Size = 12
> .Font.Bold = True
> Range("F2").FormulaR1C1 = "Department/Division:"
> End With
> With Range("H2:L2")
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlBottom
> .Merge
> .Borders(xlDiagonalDown).LineStyle = xlNone
> .Borders(xlDiagonalUp).LineStyle = xlNone
> .Borders(xlEdgeTop).LineStyle = xlContinuous
> .Borders(xlEdgeBottom).LineStyle = xlContinuous
> .Borders(xlEdgeRight).LineStyle = xlContinuous
> End With
> With Range("M2")
> .HorizontalAlignment = xlLeft
> .VerticalAlignment = xlBottom
> .Font.Name = "Arial"
> .Font.Size = 12
> .Font.Bold = True
> Range("M2").FormulaR1C1 = "Date:"
> End With
> With Range("N2:Q2")
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlBottom
> .Merge
> .Borders(xlDiagonalDown).LineStyle = xlNone
> .Borders(xlDiagonalUp).LineStyle = xlNone
> .Borders(xlEdgeTop).LineStyle = xlContinuous
> .Borders(xlEdgeBottom).LineStyle = xlContinuous
> .Borders(xlEdgeRight).LineStyle = xlContinuous
> End With
> With Range("A3:A4,B3:E4,K3:N3")
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlCenter
> .Merge
> .Borders(xlDiagonalDown).LineStyle = xlNone
> .Borders(xlDiagonalUp).LineStyle = xlNone
> .Borders(xlEdgeTop).LineStyle = xlContinuous
> .Borders(xlEdgeBottom).LineStyle = xlContinuous
> .Borders(xlEdgeRight).LineStyle = xlContinuous
> .Borders(xlEdgeLeft).LineStyle = xlContinuous
> .Font.Name = "Arial"
> .Font.Size = 9
> .Font.Bold = True
> Range("A3").FormulaR1C1 = "MSDS#"
> Range("B3").FormulaR1C1 = "Product Name"
> Range("K3").FormulaR1C1 = "NFPA/HMIS Rating"
> End With
> With Range("F3:G4")
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlCenter
> .Merge
> .Borders(xlDiagonalDown).LineStyle = xlNone
> .Borders(xlDiagonalUp).LineStyle = xlNone
> .Borders(xlEdgeTop).LineStyle = xlContinuous
> .Borders(xlEdgeBottom).LineStyle = xlContinuous
> .Borders(xlEdgeRight).LineStyle = xlContinuous
> .Borders(xlEdgeLeft).LineStyle = xlContinuous
> .WrapText = True
> .Font.Name = "Arial"
> .Font.Size = 8
> .Font.Bold = True
> Range("F3").FormulaR1C1 = "Manufacturers Name Phone Number"
> End With
> With Range("H3:H4,I3:I4,J3:J4,O3:O4,P3:P4,Q3:Q4")
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlTop
> .Merge
> .Borders(xlDiagonalDown).LineStyle = xlNone
> .Borders(xlDiagonalUp).LineStyle = xlNone
> .Borders(xlEdgeTop).LineStyle = xlContinuous
> .Borders(xlEdgeBottom).LineStyle = xlContinuous
> .Borders(xlEdgeRight).LineStyle = xlContinuous
> .Borders(xlEdgeLeft).LineStyle = xlContinuous
> .WrapText = True
> .Font.Name = "Arial"
> .Font.Size = 8
> .Font.Bold = True
> Range("H3").FormulaR1C1 = "A / I"
> Range("I3").FormulaR1C1 = "EHS (302) YES/NO"
> Range("J3").FormulaR1C1 = "Toxic (313) YES/NO"
> Range("O3").FormulaR1C1 = "Disposal Code R/Y/G"
> Range("P3").FormulaR1C1 = "Quantity on Hand"
> Range("Q3").FormulaR1C1 = "Date of Inventory"
> End With
> With Range("K4,L4,M4,N4")
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlTop
> .Borders(xlDiagonalDown).LineStyle = xlNone
> .Borders(xlDiagonalUp).LineStyle = xlNone
> .Borders(xlEdgeTop).LineStyle = xlContinuous
> .Borders(xlEdgeBottom).LineStyle = xlContinuous
> .Borders(xlEdgeRight).LineStyle = xlContinuous
> .Borders(xlEdgeLeft).LineStyle = xlContinuous
> .Font.Name = "Arial"
> .Font.Size = 8
> .Font.Bold = True
> Range("K4").FormulaR1C1 = "Fire"
> Range("L4").FormulaR1C1 = "Health"
> Range("M4").FormulaR1C1 = "React"
> Range("N4").FormulaR1C1 = "Specific"
> End With
> Call formatrows
> End Sub
> Sub formatrows()
> With Range("A5:A71,H5:H17")
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlBottom
> .Borders(xlDiagonalDown).LineStyle = xlNone
> .Borders(xlDiagonalUp).LineStyle = xlNone
> .Borders(xlEdgeLeft).LineStyle = xlContinuous
> .Borders(xlEdgeTop).LineStyle = xlContinuous
> .Borders(xlEdgeBottom).LineStyle = xlContinuous
> .Borders(xlEdgeRight).LineStyle = xlContinuous
> .Borders(xlInsideHorizontal).LineStyle = xlContinuous
> End With
> With
> Range("B5:E5,B6:E6,B7:E7,B8:E8,B9:E9,B10:E10,B11:E11,B12:E12,B13:E13,B14:E14,B15:E15,B16:E16,B17:E17")
> .Font.Name = "Arial"
> .Font.Size = 9
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlBottom
> .Merge
> .Borders(xlDiagonalDown).LineStyle = xlNone
> .Borders(xlDiagonalUp).LineStyle = xlNone
> .Borders(xlEdgeLeft).LineStyle = xlContinuous
> .Borders(xlEdgeTop).LineStyle = xlContinuous
> .Borders(xlEdgeBottom).LineStyle = xlContinuous
> .Borders(xlEdgeRight).LineStyle = xlContinuous
> End With
> With
> Range("F5:G5,F6:G6,F7:G7,F8:G8,F9:G9,F10:G10,F11:G11,F12:G12,F13:G13,F14:G14,F15:G15,F16:G16,F17:G17")
> .Font.Name = "Arial"
> .Font.Size = 8
> .HorizontalAlignment = xlLeft
> .VerticalAlignment = xlBottom
> .Merge
> .Borders(xlDiagonalDown).LineStyle = xlNone
> .Borders(xlDiagonalUp).LineStyle = xlNone
> .Borders(xlEdgeLeft).LineStyle = xlContinuous
> .Borders(xlEdgeTop).LineStyle = xlContinuous
> .Borders(xlEdgeBottom).LineStyle = xlContinuous
> .Borders(xlEdgeRight).LineStyle = xlContinuous
> End With
> With Range("I5:J17")
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlBottom
> .Borders(xlDiagonalDown).LineStyle = xlNone
> .Borders(xlDiagonalUp).LineStyle = xlNone
> .Borders(xlEdgeLeft).LineStyle = xlContinuous
> .Borders(xlEdgeTop).LineStyle = xlContinuous
> .Borders(xlEdgeBottom).LineStyle = xlContinuous
> .Borders(xlEdgeRight).LineStyle = xlContinuous
> .Borders(xlInsideHorizontal).LineStyle = xlContinuous
> .Borders(xlInsideVertical).LineStyle = xlContinuous
> .NumberFormat = """Yes"";""Yes"";""No"""
> End With
> With Range("K5:M17,N5:O17,P5:P17")
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlBottom
> .Borders(xlDiagonalDown).LineStyle = xlNone
> .Borders(xlDiagonalUp).LineStyle = xlNone
> .Borders(xlEdgeLeft).LineStyle = xlContinuous
> .Borders(xlEdgeTop).LineStyle = xlContinuous
> .Borders(xlEdgeBottom).LineStyle = xlContinuous
> .Borders(xlEdgeRight).LineStyle = xlContinuous
> .Borders(xlInsideHorizontal).LineStyle = xlContinuous
> .Borders(xlInsideVertical).LineStyle = xlContinuous
> .NumberFormat = "0"
> End With
> With Range("Q5:Q17")
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlBottom
> .Borders(xlDiagonalDown).LineStyle = xlNone
> .Borders(xlDiagonalUp).LineStyle = xlNone
> .Borders(xlEdgeLeft).LineStyle = xlContinuous
> .Borders(xlEdgeTop).LineStyle = xlContinuous
> .Borders(xlEdgeBottom).LineStyle = xlContinuous
> .Borders(xlEdgeRight).LineStyle = xlContinuous
> .Borders(xlInsideHorizontal).LineStyle = xlContinuous
> .NumberFormat = "[$-409]d-mmm-yy;@"
> End With
> End Sub

I am trying to use the code I posted as a page format for my data, so if I
have 28 rows of data I want it to produce 2 pages.

"Jim Thomlinson" wrote:

> Your question is a little thin on details. Are you wanting to know how to
> validate that the criteria selected results in a data set of 14 or less items
> or did you want to know how to expand your report to allow for more than 14
> items???
>
> As an aside you can replace all of this code
> .Borders(xlEdgeLeft).LineStyle = xlContinuous
> .Borders(xlEdgeTop).LineStyle = xlContinuous
> .Borders(xlEdgeBottom).LineStyle = xlContinuous
> .Borders(xlEdgeRight).LineStyle = xlContinuous
> .Borders(xlInsideHorizontal).LineStyle = xlContinuous
> .Borders(xlInsideVertical).LineStyle = xlContinuous
> with
> .Borders.LineStyle = xlContinuous
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Mekinnik" wrote:
>
> > I am trying to create a report page similar to what Acess does, however I do
> > not know how to use Acess, so I want to do it in Excel. My vision is to
> > programmaticlly take all my rows of data and paste it onto a report, using a
> > userform selection as my filter criteria and here is where the problem lies,
> > the report can only hold 14 rows of data on a single page, I would like to
> > know how if possible can I accomplish this task? Here is the code I currently
> > have which will show how I want the report to look..
> >
> > Public Sub FormatHeaders()
> > 'format row and columns
> > Rows("1:1").RowHeight = 45
> > Rows("2:2").RowHeight = 15.75
> > Rows("3:3").RowHeight = 21.75
> > Rows("4:4").RowHeight = 13
> > Rows("5:21").RowHeight = 33
> > Columns("A:A").ColumnWidth = 6.57
> > Columns("E:F").ColumnWidth = 8.43
> > Columns("G:G").ColumnWidth = 15
> > Columns("H:H").ColumnWidth = 2.96
> > Columns("L:M").ColumnWidth = 6.14
> > Columns("N:N").ColumnWidth = 6.29
> > Range("C:C,O:O,P:P").ColumnWidth = 6.86
> > Columns("Q:Q").ColumnWidth = 8.71
> > Range("B:B,D,I:K").ColumnWidth = 6
> >
> > 'formats row 1 for heading
> > With Range("A1:Q1")
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlBottom
> > .Merge
> > .Borders(xlDiagonalDown).LineStyle = xlNone
> > .Borders(xlDiagonalUp).LineStyle = xlNone
> > .Borders(xlEdgeTop).LineStyle = xlContinuous
> > .Borders(xlEdgeBottom).LineStyle = xlContinuous
> > .Borders(xlEdgeRight).LineStyle = xlContinuous
> > .Borders(xlEdgeLeft).LineStyle = xlContinuous
> > .Font.Name = "Arial"
> > .Font.Size = 36
> > .Font.Bold = True
> > Range("A1").FormulaR1C1 = "Hazardous Material Inventory"
> > End With
> > 'formats row 2
> > With Range("A2")
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlBottom
> > .Font.Name = "Arial"
> > .Font.Size = 12
> > .Font.Bold = True
> > Range("A2").FormulaR1C1 = "Unit:"
> > End With
> > With Range("B2:E2")
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlBottom
> > .Merge
> > .Font.Name = "Arial"
> > .Font.Size = 12
> > End With
> > With Range("A2:E2")
> > .Borders(xlDiagonalDown).LineStyle = xlNone
> > .Borders(xlDiagonalUp).LineStyle = xlNone
> > .Borders(xlEdgeTop).LineStyle = xlContinuous
> > .Borders(xlEdgeBottom).LineStyle = xlContinuous
> > .Borders(xlEdgeRight).LineStyle = xlContinuous
> > .Borders(xlEdgeLeft).LineStyle = xlContinuous
> > End With
> > With Range("F2:G2")
> > .HorizontalAlignment = xlLeft
> > .VerticalAlignment = xlBottom
> > .Borders(xlDiagonalDown).LineStyle = xlNone
> > .Borders(xlDiagonalUp).LineStyle = xlNone
> > .Borders(xlEdgeTop).LineStyle = xlContinuous
> > .Borders(xlEdgeBottom).LineStyle = xlContinuous
> > .Borders(xlEdgeLeft).LineStyle = xlContinuous
> > .Font.Name = "Arial"
> > .Font.Size = 12
> > .Font.Bold = True
> > Range("F2").FormulaR1C1 = "Department/Division:"
> > End With
> > With Range("H2:L2")
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlBottom
> > .Merge
> > .Borders(xlDiagonalDown).LineStyle = xlNone
> > .Borders(xlDiagonalUp).LineStyle = xlNone
> > .Borders(xlEdgeTop).LineStyle = xlContinuous
> > .Borders(xlEdgeBottom).LineStyle = xlContinuous
> > .Borders(xlEdgeRight).LineStyle = xlContinuous
> > End With
> > With Range("M2")
> > .HorizontalAlignment = xlLeft
> > .VerticalAlignment = xlBottom
> > .Font.Name = "Arial"
> > .Font.Size = 12
> > .Font.Bold = True
> > Range("M2").FormulaR1C1 = "Date:"
> > End With
> > With Range("N2:Q2")
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlBottom
> > .Merge
> > .Borders(xlDiagonalDown).LineStyle = xlNone
> > .Borders(xlDiagonalUp).LineStyle = xlNone
> > .Borders(xlEdgeTop).LineStyle = xlContinuous
> > .Borders(xlEdgeBottom).LineStyle = xlContinuous
> > .Borders(xlEdgeRight).LineStyle = xlContinuous
> > End With
> > With Range("A3:A4,B3:E4,K3:N3")
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlCenter
> > .Merge
> > .Borders(xlDiagonalDown).LineStyle = xlNone
> > .Borders(xlDiagonalUp).LineStyle = xlNone
> > .Borders(xlEdgeTop).LineStyle = xlContinuous
> > .Borders(xlEdgeBottom).LineStyle = xlContinuous
> > .Borders(xlEdgeRight).LineStyle = xlContinuous
> > .Borders(xlEdgeLeft).LineStyle = xlContinuous
> > .Font.Name = "Arial"
> > .Font.Size = 9
> > .Font.Bold = True
> > Range("A3").FormulaR1C1 = "MSDS#"
> > Range("B3").FormulaR1C1 = "Product Name"
> > Range("K3").FormulaR1C1 = "NFPA/HMIS Rating"
> > End With
> > With Range("F3:G4")
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlCenter
> > .Merge
> > .Borders(xlDiagonalDown).LineStyle = xlNone
> > .Borders(xlDiagonalUp).LineStyle = xlNone
> > .Borders(xlEdgeTop).LineStyle = xlContinuous
> > .Borders(xlEdgeBottom).LineStyle = xlContinuous
> > .Borders(xlEdgeRight).LineStyle = xlContinuous
> > .Borders(xlEdgeLeft).LineStyle = xlContinuous
> > .WrapText = True
> > .Font.Name = "Arial"
> > .Font.Size = 8
> > .Font.Bold = True
> > Range("F3").FormulaR1C1 = "Manufacturers Name Phone Number"
> > End With
> > With Range("H3:H4,I3:I4,J3:J4,O3:O4,P3:P4,Q3:Q4")
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlTop
> > .Merge
> > .Borders(xlDiagonalDown).LineStyle = xlNone
> > .Borders(xlDiagonalUp).LineStyle = xlNone
> > .Borders(xlEdgeTop).LineStyle = xlContinuous
> > .Borders(xlEdgeBottom).LineStyle = xlContinuous
> > .Borders(xlEdgeRight).LineStyle = xlContinuous
> > .Borders(xlEdgeLeft).LineStyle = xlContinuous
> > .WrapText = True
> > .Font.Name = "Arial"
> > .Font.Size = 8
> > .Font.Bold = True
> > Range("H3").FormulaR1C1 = "A / I"
> > Range("I3").FormulaR1C1 = "EHS (302) YES/NO"
> > Range("J3").FormulaR1C1 = "Toxic (313) YES/NO"
> > Range("O3").FormulaR1C1 = "Disposal Code R/Y/G"
> > Range("P3").FormulaR1C1 = "Quantity on Hand"
> > Range("Q3").FormulaR1C1 = "Date of Inventory"
> > End With
> > With Range("K4,L4,M4,N4")
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlTop
> > .Borders(xlDiagonalDown).LineStyle = xlNone
> > .Borders(xlDiagonalUp).LineStyle = xlNone
> > .Borders(xlEdgeTop).LineStyle = xlContinuous
> > .Borders(xlEdgeBottom).LineStyle = xlContinuous
> > .Borders(xlEdgeRight).LineStyle = xlContinuous
> > .Borders(xlEdgeLeft).LineStyle = xlContinuous
> > .Font.Name = "Arial"
> > .Font.Size = 8
> > .Font.Bold = True
> > Range("K4").FormulaR1C1 = "Fire"
> > Range("L4").FormulaR1C1 = "Health"
> > Range("M4").FormulaR1C1 = "React"
> > Range("N4").FormulaR1C1 = "Specific"
> > End With
> > Call formatrows
> > End Sub
> > Sub formatrows()
> > With Range("A5:A71,H5:H17")
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlBottom
> > .Borders(xlDiagonalDown).LineStyle = xlNone
> > .Borders(xlDiagonalUp).LineStyle = xlNone
> > .Borders(xlEdgeLeft).LineStyle = xlContinuous
> > .Borders(xlEdgeTop).LineStyle = xlContinuous
> > .Borders(xlEdgeBottom).LineStyle = xlContinuous
> > .Borders(xlEdgeRight).LineStyle = xlContinuous
> > .Borders(xlInsideHorizontal).LineStyle = xlContinuous
> > End With
> > With
> > Range("B5:E5,B6:E6,B7:E7,B8:E8,B9:E9,B10:E10,B11:E11,B12:E12,B13:E13,B14:E14,B15:E15,B16:E16,B17:E17")
> > .Font.Name = "Arial"
> > .Font.Size = 9
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlBottom
> > .Merge
> > .Borders(xlDiagonalDown).LineStyle = xlNone
> > .Borders(xlDiagonalUp).LineStyle = xlNone
> > .Borders(xlEdgeLeft).LineStyle = xlContinuous
> > .Borders(xlEdgeTop).LineStyle = xlContinuous
> > .Borders(xlEdgeBottom).LineStyle = xlContinuous
> > .Borders(xlEdgeRight).LineStyle = xlContinuous
> > End With
> > With
> > Range("F5:G5,F6:G6,F7:G7,F8:G8,F9:G9,F10:G10,F11:G11,F12:G12,F13:G13,F14:G14,F15:G15,F16:G16,F17:G17")
> > .Font.Name = "Arial"
> > .Font.Size = 8
> > .HorizontalAlignment = xlLeft
> > .VerticalAlignment = xlBottom
> > .Merge
> > .Borders(xlDiagonalDown).LineStyle = xlNone
> > .Borders(xlDiagonalUp).LineStyle = xlNone
> > .Borders(xlEdgeLeft).LineStyle = xlContinuous
> > .Borders(xlEdgeTop).LineStyle = xlContinuous
> > .Borders(xlEdgeBottom).LineStyle = xlContinuous
> > .Borders(xlEdgeRight).LineStyle = xlContinuous
> > End With
> > With Range("I5:J17")
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlBottom
> > .Borders(xlDiagonalDown).LineStyle = xlNone
> > .Borders(xlDiagonalUp).LineStyle = xlNone
> > .Borders(xlEdgeLeft).LineStyle = xlContinuous
> > .Borders(xlEdgeTop).LineStyle = xlContinuous
> > .Borders(xlEdgeBottom).LineStyle = xlContinuous
> > .Borders(xlEdgeRight).LineStyle = xlContinuous
> > .Borders(xlInsideHorizontal).LineStyle = xlContinuous
> > .Borders(xlInsideVertical).LineStyle = xlContinuous
> > .NumberFormat = """Yes"";""Yes"";""No"""
> > End With
> > With Range("K5:M17,N5:O17,P5:P17")
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlBottom
> > .Borders(xlDiagonalDown).LineStyle = xlNone
> > .Borders(xlDiagonalUp).LineStyle = xlNone
> > .Borders(xlEdgeLeft).LineStyle = xlContinuous
> > .Borders(xlEdgeTop).LineStyle = xlContinuous
> > .Borders(xlEdgeBottom).LineStyle = xlContinuous
> > .Borders(xlEdgeRight).LineStyle = xlContinuous
> > .Borders(xlInsideHorizontal).LineStyle = xlContinuous
> > .Borders(xlInsideVertical).LineStyle = xlContinuous
> > .NumberFormat = "0"
> > End With
> > With Range("Q5:Q17")
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlBottom
> > .Borders(xlDiagonalDown).LineStyle = xlNone
> > .Borders(xlDiagonalUp).LineStyle = xlNone
> > .Borders(xlEdgeLeft).LineStyle = xlContinuous
> > .Borders(xlEdgeTop).LineStyle = xlContinuous
> > .Borders(xlEdgeBottom).LineStyle = xlContinuous
> > .Borders(xlEdgeRight).LineStyle = xlContinuous
> > .Borders(xlInsideHorizontal).LineStyle = xlContinuous
> > .NumberFormat = "[$-409]d-mmm-yy;@"
> > End With
> > End Sub