bcomhari
09-04-2014, 12:02 PM
Dear Team,
I am working in excel vba to copy a unique records and paste to Main worksheet
Here i have 2 sheets(1. Editable Pipeline data(Main Sheet) 2. Trace inputs ) so here i want to compare my Editable Pipeline data with Trace inputs and copy unique data values and paste into end of the cells of Main sheet (editable pipeline data)
2nd step is do the vlookup from the Editable data to Trace inputs and fetch the other related info
below is the code , here i am getting error becoz i am extending cell range, so please help here
sample excel sheet also attached please help
please help, here is the record macro
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).SetFirstPriori ty 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
I am working in excel vba to copy a unique records and paste to Main worksheet
Here i have 2 sheets(1. Editable Pipeline data(Main Sheet) 2. Trace inputs ) so here i want to compare my Editable Pipeline data with Trace inputs and copy unique data values and paste into end of the cells of Main sheet (editable pipeline data)
2nd step is do the vlookup from the Editable data to Trace inputs and fetch the other related info
below is the code , here i am getting error becoz i am extending cell range, so please help here
sample excel sheet also attached please help
please help, here is the record macro
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).SetFirstPriori ty 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