HTML Code:
[CODE]Sub Macro8() ' ' Macro8 Macro ' Myworkings ':
' Sheets("Trace Inputs").Select Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.End(xlUp).Select Selection.End(xlToRight).Select Range("AR1").Select ActiveSheet.Paste Selection.End(xlToLeft).Select Application.CutCopyMode = False Sheets("Editable Pipeline data").Select Range("B15").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Workings").Select Range("A2").Select ActiveSheet.Paste Range("B2").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "Masterdata" Range("B2").Select Selection.Copy Range("A2").Select Selection.End(xlDown).Select Range("B177").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Selection.End(xlUp).Select Range("B1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "Data" Range("B2").Select Sheets("Trace Inputs").Select Range("F2").Select Range(Selection, Selection.End(xlDown)).Select
Selection.Copy Sheets("Workings").Select Range("A2").Select Selection.End(xlDown).Select Range("A178").Select ActiveSheet.Paste Range("B178").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "Present Pipeline" Range("B178").Select Selection.Copy Range("A178").Select Selection.End(xlDown).Select Range("B384").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Selection.End(xlUp).Select Application.CutCopyMode = False Selection.AutoFilter Columns("A:A").Select Selection.FormatConditions.AddUniqueValues Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority Selection.FormatConditions(1).DupeUnique = xlDuplicate With Selection.FormatConditions(1).Font .Color = -16383844 .TintAndShade = 0 End With With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13551615 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False ActiveSheet.Range("$A$1:$B$384").AutoFilter Field:=2, Criteria1:= _ "Present Pipeline" ActiveSheet.Range("$A$1:$B$384").AutoFilter Field:=1, Criteria1:=RGB(255, _ 255, 255), Operator:=xlFilterCellColor Range("A244").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Editable Pipeline data").Select Selection.End(xlUp).Select Range("B15").Select Selection.End(xlDown).Select Range("B191").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("C189").Select Sheets("Workings").Select Range("A1").Select Application.CutCopyMode = False Selection.AutoFilter Columns("A:B").Select Selection.Delete Shift:=xlToLeft Sheets("Editable Pipeline data").Select Range("B190").Select Selection.End(xlUp).Select Range("B15").Select Selection.End(xlDown).Select Range("B190").Select Selection.End(xlUp).Select Range("C15").Select Selection.End(xlDown).Select Range("C191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Trace Inputs'!R2C6:R208C15,10,0)" Range("C191").Select Sheets("Trace Inputs").Select Range("F195").Select Selection.End(xlUp).Select Range("AR1").Select Sheets("Editable Pipeline data").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Trace Inputs'!R2C6:R208C44,10,0)" Range("D191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],'Trace Inputs'!R2C6:R208C44,11,0)" Range("C191:D191").Select Selection.Cut Range("E191").Select ActiveSheet.Paste Range("F191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],'Trace Inputs'!R2C6:R208C44,14,0)" Range("F191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],'Trace Inputs'!R2C6:R208C44,13,0)" Range("F191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],'Trace Inputs'!R2C6:R208C44,12,0)" Range("G191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],'Trace Inputs'!R2C6:R208C44,13,0)" Range("H191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],'Trace Inputs'!R2C6:R208C44,14,0)" Range("I191").Select Sheets("Trace Inputs").Select Range("AF1").Select Selection.End(xlToLeft).Select Range("F1:AG1").Select Sheets("Editable Pipeline data").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],'Trace Inputs'!R2C6:R208C44,19,0)" Range("J191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],'Trace Inputs'!R2C6:R208C44,28,0)" Range("K191").Select Sheets("Trace Inputs").Select Range("F1:AI1").Select Range(Selection, Selection.End(xlToRight)).Select Sheets("Editable Pipeline data").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-9],'Trace Inputs'!R2C6:R208C44,30,0)" Range("Q191").Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],'Trace Inputs'!R2C6:R208C44,39,0)" Range("A191").Select Sheets("Trace Inputs").Select Range("AE1").Select Selection.End(xlToLeft).Select Range("F1:AF1").Select Sheets("Editable Pipeline data").Select Range("Q191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],'Trace Inputs'!R2C6:R208C44,27,0)" Range("A191").Select Selection.Copy Range("A192").Select ActiveSheet.Paste Range("E191:Q191").Select Application.CutCopyMode = False Selection.Copy Range("E192").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save Range("D187").Select Selection.End(xlUp).Select Range("D15").Select Selection.End(xlDown).Select Range("D191").Select
End Sub[/CODE]