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,  '" &amp; vbaPath &amp; vbaFile &amp; Orderlist2017 &amp; "'!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,  '" &amp; vbaPath2 &amp; vbaFile2 &amp; Orderlist2017 &amp; "'!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
This: 

With ThisWorkbook.Sheets("Orderlist P25 2017")
Range("Z10").Formula = "=VLookup(C10,  '" &amp; vbaPath &amp; vbaFile &amp; Orderlist2017 &amp; "'!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],  '" &amp; vbaPath &amp; vbaFile &amp; Orderlist2017 &amp; "'!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)