Select.įurther to your question, how do you run two codes on two different sheets? It's not too hard - just create two Worksheet variables, and use With statements.įor example, here's a code that places "A" in Sheet1, cell A1, and "A" in Sheet2, cell A2: Sub test()
Here's a good SO thread that outlines how to avoid using. So far all it does it run both on the active sheet and that makes everything mess up.Īha, you've found one of the pitfalls in using. Is there a way? So far all it does it run both on the active sheet and that makes everything mess up.Īll I want is to re-order the things on sheet 2 so when I do the V lookup on sheet 1 they are in the right order! Selection.AutoFill Destination:=Range("R2:R" & Cells(Rows.Count, "Z").End(xlUp).Row) Selection.AutoFill Destination:=Range("Q2:Q" & Cells(Rows.Count, "Z").End(xlUp).Row)ĪctiveCell.FormulaR1C1 = "=VLOOKUP(RC,Sheet1!C:C,4,FALSE)" Selection.AutoFill Destination:=Range("P2:P" & Cells(Rows.Count, "Z").End(xlUp).Row)ĪctiveCell.FormulaR1C1 = "=VLOOKUP(RC,Sheet1!C:C,3,FALSE)" Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAboveĪctiveCell.FormulaR1C1 = "=VLOOKUP(RC,Sheet1!C:C,2,FALSE)" 'This will add three columns for the Item2, Item3, and the Item4 datapoints based on Item1 in column A. SearchOrder:=xlB圜olumns, SearchDirection:=xlNext, MatchCase:=False)
Set Found = Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, _ 'Place the column headers in the end result order you want.ĪrrColOrder = Array("*Item1*", "*Item2*", "*Item3*", "*Item4*")įor ndx = LBound(arrColOrder) To UBound(arrColOrder) I want it to run this code on sheet2: Dim arrColOrder As Variant, ndx As Integer I was wondering if there is a way to have my macro do the below: