2
\$\begingroup\$

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
Improve this question
\$\endgroup\$
3
  • 1
    \$\begingroup\$ How to avoid using .Select would be a great start. \$\endgroup\$
    – FreeMan
    Aug 30, 2018 at 16:10
  • \$\begingroup\$ 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. \$\endgroup\$
    – n4pster
    Aug 30, 2018 at 17:08
  • 1
    \$\begingroup\$ 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/… \$\endgroup\$
    – nutsch
    Aug 30, 2018 at 18:00

1 Answer 1

Reset to default
1
\$\begingroup\$

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)

Improve this answer
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.