Sub WBS_SUMMARY()
Dim BOELst As Long
Dim WBSLst1 As Long
Dim WBSLstRow As Long
Dim WBSDLst As Long
Dim WBSLstCol As Long
BOELst = Sheet9.Range("C" & Rows.Count).End(xlUp).Row
Sheet8.Select
Cells.Select
Selection.ClearContents
Sheets("54_TPL_01_02").Select
Range("G11:G" & BOELst).Select
Selection.Copy
Sheets("WBS_SUMMARY").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Range("$A:$A").RemoveDuplicates Columns:=1, Header:=xlNo
ActiveWorkbook.Worksheets("WBS_SUMMARY").Sort.SortFields.Add Key:=Range("A:A") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("WBS_SUMMARY").Sort
.SetRange Range("A:A")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
WBSLst1 = Sheet8.Range("A" & Rows.Count).End(xlUp).Row
Range("A1:A" & WBSLst1).Select
Selection.Copy
Range("F1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1:A" & WBSLst1).Select
Selection.ClearContents
WBSDLst = Sheet2.Range("C" & Rows.Count).End(xlUp).Row
'WBS NUMBER
Sheet2.Select
Range("C9:C" & WBSDLst).Select
Selection.Copy
Sheet8.Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'WBS TITLE
Sheet2.Select
Range("G9:G" & WBSDLst).Select
Selection.Copy
Sheet8.Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'WBS Level
Sheet2.Select
Range("D9:D" & WBSDLst).Select
Selection.Copy
Sheet8.Select
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'RELATION
Sheet2.Select
Range("O9:O" & WBSDLst).Select
Selection.Copy
Sheet8.Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'PARENT WBE
Sheet2.Select
Range("P9:P" & WBSDLst).Select
Selection.Copy
Sheet8.Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheet8.Select
Range("A1").FormulaR1C1 = "WBS NUMBER"
Range("B1").FormulaR1C1 = "PARENT"
Range("C1").FormulaR1C1 = "TITLE"
Range("D1").FormulaR1C1 = "RELATION"
Range("E1").FormulaR1C1 = "PARENT"
Range("A1:E1").Select
Selection.Font.Underline = xlUnderlineStyleNone
Selection.Font.Underline = xlUnderlineStyleSingle
Columns("A:A").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:E").EntireColumn.AutoFit
Columns("A:C").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("D:E").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
'Color Fonts PLC
Range("F1:BA1").Select
With Selection.Font
.Color = -3394816
.TintAndShade = 0
End With
' WBSLstRow = Sheet8.Range("A" & Rows.Count).End(xlUp).Row
WBSLstCol = Sheet8.Range(1 & Columns.Count).End(xlToLeft).Column
Range("F2", Cells(WBSLstRow, WBSLstCol)).FormulaR1C1 = _
"=IFERROR(IF(RC5=""Child"",(SUMIFS('54_TPL_01_02'!C12,'54_TPL_01_02'!C7,R1C,'54_TPL_01_02'!C3,RC1)),IF(RC5=""Parent"",SUMIF(R[1]C2:R5000C2,RC1,R[1]C:R5000C),0)),0)"
' Columns("F:" & WBSLstCol).Select
' Selection.ColumnWidth = 6
' Range("A2").Select
End Sub