0

So, for my work we are constantly cross referencing Part #'s. I am looking to create a VBA that will VLOOKUP column 13, 14, 23, 24 From sheet 2 and copy and paste values to sheet 1 R-U. I tried recording the macros on my first sheet and it worked fine, but when I try it on a different workbook it doesn't seem to work. Below is what I have. Sub Vlookupandreplacetemplate() ' ' Vlookupandreplacetemplate Macro '

'

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16],Sheet2!C[-17]:C[64],13,0)"
Selection.AutoFill Destination:=Range("R2:R401")
Range("R2:R401").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16],Sheet2!C[-17]:C[64],13,0)"
Range("S2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-17],Sheet2!C[-18]:C[63],14,0)"
Range("S2").Select
Selection.AutoFill Destination:=Range("S2:S401")
Range("S2:S401").Select
Range("T2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-18],Sheet2!C[-19]:C[62],23,0)"
Range("T2").Select
Selection.AutoFill Destination:=Range("T2:T401")
Range("T2:T401").Select
Range("U2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-19],Sheet2!C[-20]:C[61],24,0)"
Range("U3").Select
ActiveCell.FormulaR1C1 = ""
Range("U2").Select
Selection.AutoFill Destination:=Range("U2:U401")
Range("U2:U401").Select
Columns("R:U").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

End Sub

chance marshall
  • 1
  • 1
  • What does "it doesn't seem to work." mean?? – nicomp Jul 26 '18 at 20:21
  • There is nothing wrong with the code above. It's a little bit delicate in that you need to select cell R2 in Sheet 1 before you run the code (otherwise the section of the column wont work). Also make sure your new spreadsheet has a "Sheet2". No space like there is in your question. I suspect this is where you are going wrong. If not, then please post the error you get and on what line the error occurs. – Richard Jul 26 '18 at 20:18
  • Try reading this: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Selkie Jul 26 '18 at 21:53

1 Answers1

0

Make sure your new spreadsheet has a "Sheet2". No space like there is in your question.

I suspect this is where you are going wrong.

Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
Richard
  • 439
  • 3
  • 25