Sub DBtableFormat()
'
' DBtableFormat Macro
' Converts db exports to tables
'
'
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
'convert source data to table
ws.Activate
ActiveSheet.Range("A1").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range(Selection, Selection.SpecialCells(xlLastCell)), , xlYes).Name _
= "ReqVol" & ws.Index + 3
'Range("tableReq[#All]").Select
ActiveSheet.ListObjects(1).TableStyle = "TableStyleLight14"
Next ws
End Sub
Sub newDataSheets()
'
' newDataSheet Macro
' adds new datasheet to workbook
'
'
Dim wbsList As String
'WBS listing
'wbsList = "YC.PR.AAA YE.ST.ACN ALL"
wbsList = "YC.PR.AAA YC.DP.AAA YE.ST.ACN YE.US.ACN YE.BB.SHQ YE.EE.SHQ YE.ST.SHQ YE.BB.DSQ YE.ST.DSQ YE.BB.MUS YW.BB.MUS YW.ST.ADB YW.SB.ADB YW.BB.ADB YW.ST.SAC YW.BB.SAC YW.ST.SAD YW.BB.SAD YW.ST.SAS YW.SB.SAS YW.BB.SAS YW.ST.WAC YW.BB.WAC YW.ST.SPC YW.SB.SPC YW.BB.SPC YW.ST.VIL YW.BB.VIL YW.ST.ZOO YW.BB.ZOO YW.ST.RYS YW.BB.RYS YW.ST.MUA YW.TB.MUA YW.BB.MUA"
Dim wbsArray() As String
wbsArray() = Split(wbsList)
'repeat for each location
'add charts sheet
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "CHARTS"
ActiveSheet.Range("A1") = "WBS code"
ActiveSheet.Range("B1") = "Total Requirements"
ActiveSheet.Range("C1") = "Requirements Complied"
ActiveSheet.Range("D1") = "Requirements Compliance Blank"
ActiveSheet.Range("E1") = "Total PMM's"
ActiveSheet.Range("F1") = "PMM's Complied"
ActiveSheet.Range("G1") = "PMM Compliance Blank"
ActiveSheet.Range("I2") = "Requirements"
ActiveSheet.Range("I3") = "Req.Compliances"
ActiveSheet.Range("I4") = "PMM's"
ActiveSheet.Range("I5") = "PMM Compliances"
ActiveSheet.Range("J1") = "Volume-4"
ActiveSheet.Range("J2") = "=+COUNTIF('V4'!C[-4],""Requirement"")"
ActiveSheet.Range("J3") = "=+COUNTIF('V4'!C[-4],""Req.Compliances"")"
ActiveSheet.Range("J4") = "=+COUNTIF('V4'!C[-4],""Process Method Management"")"
ActiveSheet.Range("J5") = "=+COUNTIF('V4'!C[-4],""Process Method Management compliances"")"
ActiveSheet.Range("K1") = "Volume-5"
ActiveSheet.Range("K2") = "=+COUNTIF('V5'!C[-5],""Requirement"")"
ActiveSheet.Range("K3") = "=+COUNTIF('V5'!C[-5],""Req.Compliances"")"
ActiveSheet.Range("K4") = "=+COUNTIF('V5'!C[-5],""Process Method Management"")"
ActiveSheet.Range("K5") = "=+COUNTIF('V5'!C[-5],""Process Method Management compliances"")"
ActiveSheet.Range("L1") = "Volume-6"
ActiveSheet.Range("L2") = "=+COUNTIF('V6'!C[-6],""Requirement"")"
ActiveSheet.Range("L3") = "=+COUNTIF('V6'!C[-6],""Req.Compliances"")"
ActiveSheet.Range("L4") = "=+COUNTIF('V6'!C[-6],""Process Method Management"")"
ActiveSheet.Range("L5") = "=+COUNTIF('V6'!C[-6],""Process Method Management compliances"")"
ActiveSheet.Range("M1") = "Volume-7"
ActiveSheet.Range("M2") = "=+COUNTIF('V7'!C[-7],""Requirement"")"
ActiveSheet.Range("M3") = "=+COUNTIF('V7'!C[-7],""Req.Compliances"")"
ActiveSheet.Range("M4") = "=+COUNTIF('V7'!C[-7],""Process Method Management"")"
ActiveSheet.Range("M5") = "=+COUNTIF('V7'!C[-7],""Process Method Management compliances"")"
ActiveSheet.Range("O1") = "FBS Code"
ActiveSheet.Range("O2") = "CIV-ALI"
ActiveSheet.Range("O3") = "CIV-ARC-EXT"
ActiveSheet.Range("O4") = "CIV-ARC-STN"
ActiveSheet.Range("O5") = "CIV-ATG"
ActiveSheet.Range("O6") = "CIV-CSD"
ActiveSheet.Range("O7") = "CIV-ENA"
ActiveSheet.Range("O8") = "CIV-LSC"
ActiveSheet.Range("O9") = "CIV-MEP"
ActiveSheet.Range("O10") = "CIV-STN"
ActiveSheet.Range("O11") = "CIV-STR"
ActiveSheet.Range("O12") = "CIV-TUN"
ActiveSheet.Range("O13") = "INF-EXT"
ActiveSheet.Range("O14") = "INF-INT"
ActiveSheet.Range("O15") = "EMT"
ActiveSheet.Range("O16") = "HSE"
ActiveSheet.Range("O17") = "PMT"
ActiveSheet.Range("O18") = "QMS"
ActiveSheet.Range("O19") = "ROP-MNT"
ActiveSheet.Range("O20") = "SSA"
ActiveSheet.Range("O21") = "SYS-ENG"
ActiveSheet.Range("P1") = "No.Requirements"
'
' DBtableFormat Macro
' Converts db exports to tables
'
'
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
'convert source data to table
ws.Activate
ActiveSheet.Range("A1").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range(Selection, Selection.SpecialCells(xlLastCell)), , xlYes).Name _
= "ReqVol" & ws.Index + 3
'Range("tableReq[#All]").Select
ActiveSheet.ListObjects(1).TableStyle = "TableStyleLight14"
Next ws
End Sub
Sub newDataSheets()
'
' newDataSheet Macro
' adds new datasheet to workbook
'
'
Dim wbsList As String
'WBS listing
'wbsList = "YC.PR.AAA YE.ST.ACN ALL"
wbsList = "YC.PR.AAA YC.DP.AAA YE.ST.ACN YE.US.ACN YE.BB.SHQ YE.EE.SHQ YE.ST.SHQ YE.BB.DSQ YE.ST.DSQ YE.BB.MUS YW.BB.MUS YW.ST.ADB YW.SB.ADB YW.BB.ADB YW.ST.SAC YW.BB.SAC YW.ST.SAD YW.BB.SAD YW.ST.SAS YW.SB.SAS YW.BB.SAS YW.ST.WAC YW.BB.WAC YW.ST.SPC YW.SB.SPC YW.BB.SPC YW.ST.VIL YW.BB.VIL YW.ST.ZOO YW.BB.ZOO YW.ST.RYS YW.BB.RYS YW.ST.MUA YW.TB.MUA YW.BB.MUA"
Dim wbsArray() As String
wbsArray() = Split(wbsList)
'repeat for each location
'add charts sheet
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "CHARTS"
ActiveSheet.Range("A1") = "WBS code"
ActiveSheet.Range("B1") = "Total Requirements"
ActiveSheet.Range("C1") = "Requirements Complied"
ActiveSheet.Range("D1") = "Requirements Compliance Blank"
ActiveSheet.Range("E1") = "Total PMM's"
ActiveSheet.Range("F1") = "PMM's Complied"
ActiveSheet.Range("G1") = "PMM Compliance Blank"
ActiveSheet.Range("I2") = "Requirements"
ActiveSheet.Range("I3") = "Req.Compliances"
ActiveSheet.Range("I4") = "PMM's"
ActiveSheet.Range("I5") = "PMM Compliances"
ActiveSheet.Range("J1") = "Volume-4"
ActiveSheet.Range("J2") = "=+COUNTIF('V4'!C[-4],""Requirement"")"
ActiveSheet.Range("J3") = "=+COUNTIF('V4'!C[-4],""Req.Compliances"")"
ActiveSheet.Range("J4") = "=+COUNTIF('V4'!C[-4],""Process Method Management"")"
ActiveSheet.Range("J5") = "=+COUNTIF('V4'!C[-4],""Process Method Management compliances"")"
ActiveSheet.Range("K1") = "Volume-5"
ActiveSheet.Range("K2") = "=+COUNTIF('V5'!C[-5],""Requirement"")"
ActiveSheet.Range("K3") = "=+COUNTIF('V5'!C[-5],""Req.Compliances"")"
ActiveSheet.Range("K4") = "=+COUNTIF('V5'!C[-5],""Process Method Management"")"
ActiveSheet.Range("K5") = "=+COUNTIF('V5'!C[-5],""Process Method Management compliances"")"
ActiveSheet.Range("L1") = "Volume-6"
ActiveSheet.Range("L2") = "=+COUNTIF('V6'!C[-6],""Requirement"")"
ActiveSheet.Range("L3") = "=+COUNTIF('V6'!C[-6],""Req.Compliances"")"
ActiveSheet.Range("L4") = "=+COUNTIF('V6'!C[-6],""Process Method Management"")"
ActiveSheet.Range("L5") = "=+COUNTIF('V6'!C[-6],""Process Method Management compliances"")"
ActiveSheet.Range("M1") = "Volume-7"
ActiveSheet.Range("M2") = "=+COUNTIF('V7'!C[-7],""Requirement"")"
ActiveSheet.Range("M3") = "=+COUNTIF('V7'!C[-7],""Req.Compliances"")"
ActiveSheet.Range("M4") = "=+COUNTIF('V7'!C[-7],""Process Method Management"")"
ActiveSheet.Range("M5") = "=+COUNTIF('V7'!C[-7],""Process Method Management compliances"")"
ActiveSheet.Range("O1") = "FBS Code"
ActiveSheet.Range("O2") = "CIV-ALI"
ActiveSheet.Range("O3") = "CIV-ARC-EXT"
ActiveSheet.Range("O4") = "CIV-ARC-STN"
ActiveSheet.Range("O5") = "CIV-ATG"
ActiveSheet.Range("O6") = "CIV-CSD"
ActiveSheet.Range("O7") = "CIV-ENA"
ActiveSheet.Range("O8") = "CIV-LSC"
ActiveSheet.Range("O9") = "CIV-MEP"
ActiveSheet.Range("O10") = "CIV-STN"
ActiveSheet.Range("O11") = "CIV-STR"
ActiveSheet.Range("O12") = "CIV-TUN"
ActiveSheet.Range("O13") = "INF-EXT"
ActiveSheet.Range("O14") = "INF-INT"
ActiveSheet.Range("O15") = "EMT"
ActiveSheet.Range("O16") = "HSE"
ActiveSheet.Range("O17") = "PMT"
ActiveSheet.Range("O18") = "QMS"
ActiveSheet.Range("O19") = "ROP-MNT"
ActiveSheet.Range("O20") = "SSA"
ActiveSheet.Range("O21") = "SYS-ENG"
ActiveSheet.Range("P1") = "No.Requirements"