Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Create a report page???
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 159
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 159
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
info on 400 pageson to 1 report page Kelly Excel Worksheet Functions 2 October 21st 09 07:13 PM
Saving A 30 Page Report As A PDF [email protected] Excel Discussion (Misc queries) 3 November 19th 08 05:41 PM
Create Report Robert[_32_] Excel Programming 3 February 9th 07 02:21 PM
Multiple page report in a pivottable Rayo K Excel Discussion (Misc queries) 3 April 21st 06 01:02 PM
How do I save one page out of a 80 page report in excel? bellamere New Users to Excel 2 February 15th 06 08:25 PM


All times are GMT +1. The time now is 05:14 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement