|
Create a report page???
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:E 11,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:G 11,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 |
Create a report page???
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: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:E 11,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:G 11,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 |
Create a report page???
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: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:E 11,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:G 11,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 |
| All times are GMT +1. The time now is 12:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com