# Copying data from various sources into a master list

I am working on a Master List, where I am copying data from various sources for each month into the columns Z, AC, AF, AI etc. (always separated by 2 columns). Then I copy that cell all the way down to update the values for each row. As you can see in the code below, the only difference from one section of the code to the next is:

• Change column (here Z to AC)
• Change paths which are stored in different cells (e.g. fromPath changed to fromPath2.

How can I make it more efficient?

' Update Jan 2018

fromPath = Sheets("Filepaths for P25 2017").Range("G2")
vbaPath = Sheets("Filepaths for P25 2017").Range("F2")
vbaFile = Sheets("Filepaths for P25 2017").Range("H2")
Orderlist2017 = Sheets("Filepaths for P25 2017").Range("I2")

With ThisWorkbook.Sheets("Orderlist P25 2017")
Range("Z10").Formula = "=VLookup(C10,  '" & vbaPath & vbaFile & Orderlist2017 & "'!C14:Z90, 8, False)"
Range("Z10").Select
Selection.Copy
Range("Y10").Select
Selection.End(xlDown).Select
Range("Z85").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End With

' Update Feb 2018

fromPath2 = Sheets("Filepaths for P25 2017").Range("G3")
vbaPath2 = Sheets("Filepaths for P25 2017").Range("F3")
vbaFile2 = Sheets("Filepaths for P25 2017").Range("H3")
Orderlist2017 = Sheets("Filepaths for P25 2017").Range("I3")

With ThisWorkbook.Sheets("Orderlist P25 2017")
Range("AC10").Formula = "=VLookup(C10,  '" & vbaPath2 & vbaFile2 & Orderlist2017 & "'!C14:Z90, 8, False)"
Range("AC10").Select
Selection.Copy
Range("Y10").Select
Selection.End(xlDown).Select
Range("AC85").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End With

• How to avoid using .Select would be a great start. Aug 30 '18 at 16:10
• Ok I changed all the .Select with .Filldown. But the same problem remains: I always need to manually change the paths (vbapath, vbapath2, vbapath3, vbapath4 etc..) Isnt there a way to assign variables so Excel can do the loop automatically? Hope its clear. Aug 30 '18 at 17:08
• Moving to a R1C1 reference style in your formula should handle most of the issue. Try reading on it: tduhameau.wordpress.com/2012/09/27/… Aug 30 '18 at 18:00

This:

With ThisWorkbook.Sheets("Orderlist P25 2017")
Range("Z10").Formula = "=VLookup(C10,  '" & vbaPath & vbaFile & Orderlist2017 & "'!C14:Z90, 8, False)"
Range("Z10").Select
Selection.Copy
Range("Y10").Select
Selection.End(xlDown).Select
Range("Z85").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End With


might work changed to that:

Range("Y10:Z85").FormulaR1C1 = "=VLookup(RC[-23],  '" & vbaPath & vbaFile & Orderlist2017 & "'!R[4]C[-23]:R[80]C, 8, False)"


Mastering the R1C1 reference style will change the way you deal with formulas on the VBA side. For more details, read my blog post here.

In addition, your With / End With logic is useless since you don't have any references to it (e.g. a .Range)