M
Mekinnik
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").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,P34,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,P517")
.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
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").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,P34,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,P517")
.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