Reply to thread

Message: [QUOTE="Mekinnik, post: 6295282"] 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: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 [/QUOTE]

Verification