Copying data from various sources into a master list

up vote
1
down vote

favorite

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

edited Aug 30 at 16:31

200_success

127k15148412

asked Aug 30 at 16:03

n4pster

bumped to the homepage by Community♦ 2 days ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

1

How to avoid using .Select would be a great start.
– FreeMan
Aug 30 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.
– n4pster
Aug 30 at 17:08

1

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/…
– nutsch
Aug 30 at 18:00

add a comment |

up vote
1
down vote

favorite

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

edited Aug 30 at 16:31

200_success

127k15148412

asked Aug 30 at 16:03

n4pster

bumped to the homepage by Community♦ 2 days ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

1

How to avoid using .Select would be a great start.
– FreeMan
Aug 30 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.
– n4pster
Aug 30 at 17:08

1

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/…
– nutsch
Aug 30 at 18:00

add a comment |

up vote
1
down vote

favorite

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

edited Aug 30 at 16:31

200_success

127k15148412

asked Aug 30 at 16:03

n4pster

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

performance vba excel

edited Aug 30 at 16:31

200_success

127k15148412

asked Aug 30 at 16:03

n4pster

edited Aug 30 at 16:31

200_success

127k15148412

asked Aug 30 at 16:03

n4pster

edited Aug 30 at 16:31

200_success

127k15148412

edited Aug 30 at 16:31

200_success

127k15148412

edited Aug 30 at 16:31

200_success

127k15148412

asked Aug 30 at 16:03

n4pster

asked Aug 30 at 16:03

n4pster

asked Aug 30 at 16:03

n4pster

bumped to the homepage by Community♦ 2 days ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

bumped to the homepage by Community♦ 2 days ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

1

How to avoid using .Select would be a great start.
– FreeMan
Aug 30 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.
– n4pster
Aug 30 at 17:08

1

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/…
– nutsch
Aug 30 at 18:00

add a comment |

1

How to avoid using .Select would be a great start.
– FreeMan
Aug 30 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.
– n4pster
Aug 30 at 17:08

1

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/…
– nutsch
Aug 30 at 18:00

How to avoid using .Select would be a great start.
– FreeMan
Aug 30 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.
– n4pster
Aug 30 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/…
– nutsch
Aug 30 at 18:00

add a comment |

1 Answer
1

active

oldest

votes

up vote
0
down vote

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)

answered Aug 30 at 18:06

nutsch

1163

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
});
});
}, "mathjax-editing");

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "196"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f202822%2fcopying-data-from-various-sources-into-a-master-list%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
0
down vote

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)

answered Aug 30 at 18:06

nutsch

1163

add a comment |

up vote
0
down vote

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)

answered Aug 30 at 18:06

nutsch

1163

add a comment |

up vote
0
down vote

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)

answered Aug 30 at 18:06

nutsch

1163

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)

answered Aug 30 at 18:06

nutsch

1163

answered Aug 30 at 18:06

nutsch

1163

answered Aug 30 at 18:06

nutsch

1163

answered Aug 30 at 18:06

nutsch

1163

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Code Review Stack Exchange!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

Use MathJax to format equations. MathJax reference.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Cfrtjryk