Copying data from various sources into a master list

Question

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

• FreeMan: How to avoid using .Select would be a great start.
• n4pster: 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.
• nutsch: 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/…

Answer 1

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)