Sub SortCopyPaste()
Dim wkbk1 As Workbook
Set wkbk1 = ActiveWorkbook
With ThisWorkbook.Sheets("Masterlist")
.Range("B:B").AutoFilter Field:=2, Criteria1:="<>"
'// Copy all rows except header
.UsedRange.Offset(3).SpecialCells(xlCellTypeVisible).EntireRow.copy ActiveWorkbook.ActiveSheet.Cells(4, 1)
'// Remove the autofilter
.Range("B:B").AutoFilter Field:=2
End With
Call Sort
Call header
Call otherwork
Call Notes
End Sub
Function Sort()
Dim rowcount
rowcount = ActiveSheet.UsedRange.Rows.Count
ActiveWorkbook.ActiveSheet.Sort.SortFields.clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _
(Cells(3, 7)), (Cells(rowcount, 7))), SortOn:=xlSortOnValues, order:=xlAscending, _
CustomOrder:=("TREES, SHRUBS, PERENNIALS, VINES/BULBS"), _
DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range((Cells(3, 1)), (Cells(rowcount, 7)))
.header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Function
Function otherwork()
Dim myRow As Long
Dim myCount As Long
Dim myCol As Integer
myCol = 7
myCount = ActiveSheet.UsedRange.Rows.Count
Cells(myCount - 3, 1) = "OTHER WORK"
Range((Cells(myCount + 1, 1)), (Cells(myCount + 1, myCol))).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDouble
Range((Cells(myCount + 1, 1)), (Cells(myCount + 1, myCol))).Borders(xlEdgeTop).LineStyle = XlLineStyle.xlDouble
With Cells(myCount - 3, 1)
.Font.Bold = True
End With
With Range(Cells(myCount + -3, 1), Cells(myCount + 3, 1))
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Function
Function header()
Dim myRow As Long
Dim myCount As Long
Dim myCol As Integer
myCol = 7
myCount = ActiveSheet.UsedRange.Rows.Count
'Cells(myCount + 1, 1) = "OTHER WORK"
'Range((Cells(myCount + 1, 1)), (Cells(myCount + 1, myCol + 1))).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDouble
'Range((Cells(myCount + 1, 1)), (Cells(myCount + 1, myCol + 1))).Borders(xlEdgeTop).LineStyle = XlLineStyle.xlDouble
' With Cells(myCount - 3, 1)
' .Font.Bold = True
' End With
' With Range(Cells(myCount + 1, 1), Cells(myCount + 3, 1))
' .HorizontalAlignment = xlLeft
' .VerticalAlignment = xlCenter
' .WrapText = False
' .Orientation = 0
' .AddIndent = False
' .IndentLevel = 0
' .ShrinkToFit = False
' .ReadingOrder = xlContext
' .MergeCells = False
' End With
'first border
Range("G4").Select
Selection.copy
Range("A3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A3:H3").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDouble
Range("A3:H3").Borders(xlEdgeTop).LineStyle = XlLineStyle.xlDouble
For myRow = myCount - 1 To 4 Step -1
If Cells(myRow, myCol).Value <> Cells(myRow + 1, myCol).Value Then
Range((Cells(myRow + 1, myCol)), (Cells(myRow + 2, myCol))).EntireRow.Insert
Cells(myRow + 3, myCol).Select
Selection.copy
Cells(myRow + 2, myCol - 6).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range((Cells(myRow + 2, 1)), (Cells(myRow + 2, myCol + 1))).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDouble
Range((Cells(myRow + 2, 1)), (Cells(myRow + 2, myCol + 1))).Borders(xlEdgeTop).LineStyle = XlLineStyle.xlDouble
End If
Next myRow
Call NullG
End Function
Function Notes()
Dim notecount As Integer
notecount = ActiveSheet.UsedRange.Rows.Count
'MsgBox (notecount)
Cells(notecount - 2, 1) = "NOTES"
With Cells(notecount - 2, 1)
.Font.Bold = True
End With
With Range(Cells(notecount - 2, 1), Cells(notecount + 8, 1))
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells(notecount - 1, 1) = "1.*All plant material shall conform to the “American Standards for Nursery Stock”, ANSI Z60.1-2004 by the American Nursery and Landscape Association."
Cells(notecount, 1) = "2.*All planting installation and soil preparation shall conform to the “ Landscape Specification Guidelines for Baltimore Washington Metropolitan Area”,"
Cells(notecount + 1, 1) = " most recent edition by the Landscape Contractors Association MD DC VA."
Cells(notecount + 2, 2) = "3.*Contractor shall verify locations of all underground utilities within work areas and be"
Cells(notecount + 3, 1) = " responsible for their protection. Call MISS UTILITY (1 800 257-7777) before installation commences."
Cells(notecount + 4, 1) = "4. Planting plan provides general layout only. Specific planting layout shall be directed by LA."
Cells(notecount + 5, 1) = "5.*All plant substitutions to be approved by LA."
Cells(notecount + 6, 1) = "6.*Contractor responsible for amending planting bed according to soil report or at LA direction."
Cells(notecount + 7, 1) = "7.*Plant quantities to be verified by Contractor from planting plan. Any discrepancies should be brought to the attention of the Landscape LA for verification."
End Function
Function NullG()
ActiveSheet.Columns("G:G").Delete
End Function
Bookmarks