Excel VBA - Calling/Referring to a named Range from another Sub

This is my first post on StackOverflow, so if I miss something, please let me know!

CONTEXT: I've been putting together a VBA code for the first time and so far managed to write a code from a ton of different threads and code examples from around the web. Since I'm new, I do not yet fully understand how VBA codes work - it's basically trial and error and lots of copy-pasting until I get it right. As a result, I imagine the code is much longer than it can be, and not nearly as efficient. However, since I am new, advanced codes rarely make sense to me, so I stick to more basic solutions, as I have to understand the code first and be able to integrate it in various possible ways, depending on the situation, and make it easily readable for others, should it be necessary. The reason why I am saying all this is so that you can understand my level of experience and what to expect of my capabilities of understanding VBA, so thank you in advance for your patience! I will show you some of my code below.

QUESTION: I have several ranges defined within my subs, as such:

Define range names:

X.Sheets("Sheet1").Range("B4").Name = "Type1"

X.Sheets("Sheet1").Range("B9").Name = "SubTotal1"

X.Sheets("Sheet1").Range("A6:F8").Name = "Data1"



X.Sheets("Sheet1").Range("B11").Name = "Type2"

X.Sheets("Sheet1").Range("B16").Name = "SubTotal2"

X.Sheets("Sheet1").Range("A13:F15").Name = "Data2"



X.Sheets("Sheet1").Range("B18").Name = "Type3"

X.Sheets("Sheet1").Range("B23").Name = "SubTotal3"

X.Sheets("Sheet1").Range("A20:F22").Name = "Data3"



Y.Sheets("Sheet1").Range("A4:A6").Name = "Period"

Y.Sheets("Sheet1").Range("B4:B6").Name = "Name"

Y.Sheets("Sheet1").Range("D4:D6").Name = "Code"

Y.Sheets("Sheet1").Range("E4:E6").Name = "Type"

Y.Sheets("Sheet1").Range("F4:K4").Name = "Data"

This name range is used in each and every sub (I have around 15, with around 165 more needed..) for copying and inserting information from Workbook X to Workbook Y. Since it is redundant to reuse the code for each sub, I would like to be able to put these Ranges in a separate Sub and call on it in each new Sub to save time and space.

If possible, I would also like to do the same with the following code, which refers to the ranges defined above:

'Insert Type1 Data from X:



If X.Sheets("Sheet").Range("SubTotal1").Value > 0 Then

Range("Type1").Copy

Y.Sheets("Sheet1").Range("Type").Insert xlShiftDown

Range("Data1").Copy

Y.Sheets("Sheet1").Range("Data").Insert xlShiftDown



'Insert Period:

X.Sheets("Sheet1").Range("C3").Copy

Y.Sheets("Sheet1").Range("Period").Insert xlShiftDown



'Insert Name:

X.Sheets("Sheet1").Range("C12").Copy

Y.Sheets("Sheet1").Range("Name").Insert xlShiftDown



'Insert Code Type:

X.Sheets("Sheet1").Range("C10").Copy

Y.Sheets("Sheet1").Range("Code").Insert xlShiftDown

End If

This code, and 6 more like it (Type 1-6) are also redundant in other Subs, so ideally, I would put it in a separate sub and call on it when necessary too, as it will make my code infinitely shorter. I use this at the beginning of my subs to define X and Y sheets:

Dim X As Workbook

Dim Y As Workbook



'Define workbooks:

Set X = Workbooks.Open("C:UsersuserFolderFile.xlsx")

Set Y = ThisWorkbook

If I missed something to help you get the full picture, please let me know. I know it's a long one, so thanks if you choose to take your time to respond. I appreciate it immensely!

EDIT: To give a better example of what I mean, I imagine Subs going something like this:

Sub Sub1



Call Sub "RangeNames"

Call Sub "Insert Type1 Data while referring to RangeNames"

Call Sub "Insert Type2 Data while referring to RangeNames"



End Sub

And/Or

Sub Sub2



Call Sub "RangeNames"

Call Sub "If RangeName 'SubTotal 3' > 0 then Insert Type3 Data while referring to RangeNames"



End Sub

EDIT 2:

For @SJR:

Sub Sub1

Dim X As Workbook

Dim Y As Workbook



Set X = Workbooks.Open("C:UsersuserFolderFile.xlsx")

Set Y = ThisWorkbook



X.Sheets("Sheet1").Range("B4").Name = "Type1"

X.Sheets("Sheet1").Range("B9").Name = "SubTotal1"



Y.Sheets("Sheet1").Range("E4:E6").Name = "Type"



Sub2



End Sub

Sub 2 is:

Sub Sub2



If X.Sheets("Sheet").Range("SubTotal1").Value > 0 Then <- ERROR HAPPENS HERE

Range("Type1").Copy

Y.Sheets("Sheet1").Range("Type").Insert xlShiftDown



End If



End Sub

edited Nov 20 '18 at 9:53

asked Nov 20 '18 at 5:03

Faraj

Basically, what you are trying to do is copying and inserting the same kinds of data from one workbook to another. Is that right?

– libzz
Nov 20 '18 at 7:35

Hi @libzz! Yes, that is correct. To be exact, there will be several workbooks - around 10, but they will all contain the same kind of sheets/data, with all data in fixed locations, so the rows/sheet names will not change, only the values. It is basically monthly reports coming in, with all that information needed to be copied from them and inserted into rows in a master file in different sheets based on the report information. Hope that makes sense.

– Faraj
Nov 20 '18 at 7:46

What precisely is your question? Are you asking how to pass arguments?

– SJR
Nov 20 '18 at 7:47

1

Not sure I understand, but once you have defined named ranges in one sub, you will able to reference them in other subs automatically (as you can access them via the sheet directly). You might want to read this cpearson.com/excel/writingfunctionsinvba.aspx

– SJR
Nov 20 '18 at 7:56

2

@Libzz, turns out I won't be needing arguments, since this solution worked as well. But thank you for your effort anyway!

– Faraj
Nov 20 '18 at 12:09

|
show 12 more comments

This is my first post on StackOverflow, so if I miss something, please let me know!

QUESTION: I have several ranges defined within my subs, as such:

Define range names:

X.Sheets("Sheet1").Range("B4").Name = "Type1"

X.Sheets("Sheet1").Range("B9").Name = "SubTotal1"

X.Sheets("Sheet1").Range("A6:F8").Name = "Data1"



X.Sheets("Sheet1").Range("B11").Name = "Type2"

X.Sheets("Sheet1").Range("B16").Name = "SubTotal2"

X.Sheets("Sheet1").Range("A13:F15").Name = "Data2"



X.Sheets("Sheet1").Range("B18").Name = "Type3"

X.Sheets("Sheet1").Range("B23").Name = "SubTotal3"

X.Sheets("Sheet1").Range("A20:F22").Name = "Data3"



Y.Sheets("Sheet1").Range("A4:A6").Name = "Period"

Y.Sheets("Sheet1").Range("B4:B6").Name = "Name"

Y.Sheets("Sheet1").Range("D4:D6").Name = "Code"

Y.Sheets("Sheet1").Range("E4:E6").Name = "Type"

Y.Sheets("Sheet1").Range("F4:K4").Name = "Data"

If possible, I would also like to do the same with the following code, which refers to the ranges defined above:

'Insert Type1 Data from X:



If X.Sheets("Sheet").Range("SubTotal1").Value > 0 Then

Range("Type1").Copy

Y.Sheets("Sheet1").Range("Type").Insert xlShiftDown

Range("Data1").Copy

Y.Sheets("Sheet1").Range("Data").Insert xlShiftDown



'Insert Period:

X.Sheets("Sheet1").Range("C3").Copy

Y.Sheets("Sheet1").Range("Period").Insert xlShiftDown



'Insert Name:

X.Sheets("Sheet1").Range("C12").Copy

Y.Sheets("Sheet1").Range("Name").Insert xlShiftDown



'Insert Code Type:

X.Sheets("Sheet1").Range("C10").Copy

Y.Sheets("Sheet1").Range("Code").Insert xlShiftDown

End If

Dim X As Workbook

Dim Y As Workbook



'Define workbooks:

Set X = Workbooks.Open("C:UsersuserFolderFile.xlsx")

Set Y = ThisWorkbook

If I missed something to help you get the full picture, please let me know. I know it's a long one, so thanks if you choose to take your time to respond. I appreciate it immensely!

EDIT: To give a better example of what I mean, I imagine Subs going something like this:

Sub Sub1



Call Sub "RangeNames"

Call Sub "Insert Type1 Data while referring to RangeNames"

Call Sub "Insert Type2 Data while referring to RangeNames"



End Sub

And/Or

Sub Sub2



Call Sub "RangeNames"

Call Sub "If RangeName 'SubTotal 3' > 0 then Insert Type3 Data while referring to RangeNames"



End Sub

EDIT 2:

For @SJR:

Sub Sub1

Dim X As Workbook

Dim Y As Workbook



Set X = Workbooks.Open("C:UsersuserFolderFile.xlsx")

Set Y = ThisWorkbook



X.Sheets("Sheet1").Range("B4").Name = "Type1"

X.Sheets("Sheet1").Range("B9").Name = "SubTotal1"



Y.Sheets("Sheet1").Range("E4:E6").Name = "Type"



Sub2



End Sub

Sub 2 is:

Sub Sub2



If X.Sheets("Sheet").Range("SubTotal1").Value > 0 Then <- ERROR HAPPENS HERE

Range("Type1").Copy

Y.Sheets("Sheet1").Range("Type").Insert xlShiftDown



End If



End Sub

edited Nov 20 '18 at 9:53

asked Nov 20 '18 at 5:03

Faraj

Basically, what you are trying to do is copying and inserting the same kinds of data from one workbook to another. Is that right?

– libzz
Nov 20 '18 at 7:35

Hi @libzz! Yes, that is correct. To be exact, there will be several workbooks - around 10, but they will all contain the same kind of sheets/data, with all data in fixed locations, so the rows/sheet names will not change, only the values. It is basically monthly reports coming in, with all that information needed to be copied from them and inserted into rows in a master file in different sheets based on the report information. Hope that makes sense.

– Faraj
Nov 20 '18 at 7:46

What precisely is your question? Are you asking how to pass arguments?

– SJR
Nov 20 '18 at 7:47

1

Not sure I understand, but once you have defined named ranges in one sub, you will able to reference them in other subs automatically (as you can access them via the sheet directly). You might want to read this cpearson.com/excel/writingfunctionsinvba.aspx

– SJR
Nov 20 '18 at 7:56

2

@Libzz, turns out I won't be needing arguments, since this solution worked as well. But thank you for your effort anyway!

– Faraj
Nov 20 '18 at 12:09

|
show 12 more comments

This is my first post on StackOverflow, so if I miss something, please let me know!

QUESTION: I have several ranges defined within my subs, as such:

Define range names:

X.Sheets("Sheet1").Range("B4").Name = "Type1"

X.Sheets("Sheet1").Range("B9").Name = "SubTotal1"

X.Sheets("Sheet1").Range("A6:F8").Name = "Data1"



X.Sheets("Sheet1").Range("B11").Name = "Type2"

X.Sheets("Sheet1").Range("B16").Name = "SubTotal2"

X.Sheets("Sheet1").Range("A13:F15").Name = "Data2"



X.Sheets("Sheet1").Range("B18").Name = "Type3"

X.Sheets("Sheet1").Range("B23").Name = "SubTotal3"

X.Sheets("Sheet1").Range("A20:F22").Name = "Data3"



Y.Sheets("Sheet1").Range("A4:A6").Name = "Period"

Y.Sheets("Sheet1").Range("B4:B6").Name = "Name"

Y.Sheets("Sheet1").Range("D4:D6").Name = "Code"

Y.Sheets("Sheet1").Range("E4:E6").Name = "Type"

Y.Sheets("Sheet1").Range("F4:K4").Name = "Data"

If possible, I would also like to do the same with the following code, which refers to the ranges defined above:

'Insert Type1 Data from X:



If X.Sheets("Sheet").Range("SubTotal1").Value > 0 Then

Range("Type1").Copy

Y.Sheets("Sheet1").Range("Type").Insert xlShiftDown

Range("Data1").Copy

Y.Sheets("Sheet1").Range("Data").Insert xlShiftDown



'Insert Period:

X.Sheets("Sheet1").Range("C3").Copy

Y.Sheets("Sheet1").Range("Period").Insert xlShiftDown



'Insert Name:

X.Sheets("Sheet1").Range("C12").Copy

Y.Sheets("Sheet1").Range("Name").Insert xlShiftDown



'Insert Code Type:

X.Sheets("Sheet1").Range("C10").Copy

Y.Sheets("Sheet1").Range("Code").Insert xlShiftDown

End If

Dim X As Workbook

Dim Y As Workbook



'Define workbooks:

Set X = Workbooks.Open("C:UsersuserFolderFile.xlsx")

Set Y = ThisWorkbook

If I missed something to help you get the full picture, please let me know. I know it's a long one, so thanks if you choose to take your time to respond. I appreciate it immensely!

EDIT: To give a better example of what I mean, I imagine Subs going something like this:

Sub Sub1



Call Sub "RangeNames"

Call Sub "Insert Type1 Data while referring to RangeNames"

Call Sub "Insert Type2 Data while referring to RangeNames"



End Sub

And/Or

Sub Sub2



Call Sub "RangeNames"

Call Sub "If RangeName 'SubTotal 3' > 0 then Insert Type3 Data while referring to RangeNames"



End Sub

EDIT 2:

For @SJR:

Sub Sub1

Dim X As Workbook

Dim Y As Workbook



Set X = Workbooks.Open("C:UsersuserFolderFile.xlsx")

Set Y = ThisWorkbook



X.Sheets("Sheet1").Range("B4").Name = "Type1"

X.Sheets("Sheet1").Range("B9").Name = "SubTotal1"



Y.Sheets("Sheet1").Range("E4:E6").Name = "Type"



Sub2



End Sub

Sub 2 is:

Sub Sub2



If X.Sheets("Sheet").Range("SubTotal1").Value > 0 Then <- ERROR HAPPENS HERE

Range("Type1").Copy

Y.Sheets("Sheet1").Range("Type").Insert xlShiftDown



End If



End Sub

edited Nov 20 '18 at 9:53

asked Nov 20 '18 at 5:03

Faraj

This is my first post on StackOverflow, so if I miss something, please let me know!

QUESTION: I have several ranges defined within my subs, as such:

Define range names:

X.Sheets("Sheet1").Range("B4").Name = "Type1"

X.Sheets("Sheet1").Range("B9").Name = "SubTotal1"

X.Sheets("Sheet1").Range("A6:F8").Name = "Data1"



X.Sheets("Sheet1").Range("B11").Name = "Type2"

X.Sheets("Sheet1").Range("B16").Name = "SubTotal2"

X.Sheets("Sheet1").Range("A13:F15").Name = "Data2"



X.Sheets("Sheet1").Range("B18").Name = "Type3"

X.Sheets("Sheet1").Range("B23").Name = "SubTotal3"

X.Sheets("Sheet1").Range("A20:F22").Name = "Data3"



Y.Sheets("Sheet1").Range("A4:A6").Name = "Period"

Y.Sheets("Sheet1").Range("B4:B6").Name = "Name"

Y.Sheets("Sheet1").Range("D4:D6").Name = "Code"

Y.Sheets("Sheet1").Range("E4:E6").Name = "Type"

Y.Sheets("Sheet1").Range("F4:K4").Name = "Data"

If possible, I would also like to do the same with the following code, which refers to the ranges defined above:

'Insert Type1 Data from X:



If X.Sheets("Sheet").Range("SubTotal1").Value > 0 Then

Range("Type1").Copy

Y.Sheets("Sheet1").Range("Type").Insert xlShiftDown

Range("Data1").Copy

Y.Sheets("Sheet1").Range("Data").Insert xlShiftDown



'Insert Period:

X.Sheets("Sheet1").Range("C3").Copy

Y.Sheets("Sheet1").Range("Period").Insert xlShiftDown



'Insert Name:

X.Sheets("Sheet1").Range("C12").Copy

Y.Sheets("Sheet1").Range("Name").Insert xlShiftDown



'Insert Code Type:

X.Sheets("Sheet1").Range("C10").Copy

Y.Sheets("Sheet1").Range("Code").Insert xlShiftDown

End If

Dim X As Workbook

Dim Y As Workbook



'Define workbooks:

Set X = Workbooks.Open("C:UsersuserFolderFile.xlsx")

Set Y = ThisWorkbook

If I missed something to help you get the full picture, please let me know. I know it's a long one, so thanks if you choose to take your time to respond. I appreciate it immensely!

EDIT: To give a better example of what I mean, I imagine Subs going something like this:

Sub Sub1



Call Sub "RangeNames"

Call Sub "Insert Type1 Data while referring to RangeNames"

Call Sub "Insert Type2 Data while referring to RangeNames"



End Sub

And/Or

Sub Sub2



Call Sub "RangeNames"

Call Sub "If RangeName 'SubTotal 3' > 0 then Insert Type3 Data while referring to RangeNames"



End Sub

EDIT 2:

For @SJR:

Sub Sub1

Dim X As Workbook

Dim Y As Workbook



Set X = Workbooks.Open("C:UsersuserFolderFile.xlsx")

Set Y = ThisWorkbook



X.Sheets("Sheet1").Range("B4").Name = "Type1"

X.Sheets("Sheet1").Range("B9").Name = "SubTotal1"



Y.Sheets("Sheet1").Range("E4:E6").Name = "Type"



Sub2



End Sub

Sub 2 is:

Sub Sub2



If X.Sheets("Sheet").Range("SubTotal1").Value > 0 Then <- ERROR HAPPENS HERE

Range("Type1").Copy

Y.Sheets("Sheet1").Range("Type").Insert xlShiftDown



End If



End Sub

excel vba excel-vba

edited Nov 20 '18 at 9:53

asked Nov 20 '18 at 5:03

Faraj

edited Nov 20 '18 at 9:53

asked Nov 20 '18 at 5:03

Faraj

edited Nov 20 '18 at 9:53

asked Nov 20 '18 at 5:03

Faraj

asked Nov 20 '18 at 5:03

Faraj

asked Nov 20 '18 at 5:03

Faraj

Basically, what you are trying to do is copying and inserting the same kinds of data from one workbook to another. Is that right?

– libzz
Nov 20 '18 at 7:35

Hi @libzz! Yes, that is correct. To be exact, there will be several workbooks - around 10, but they will all contain the same kind of sheets/data, with all data in fixed locations, so the rows/sheet names will not change, only the values. It is basically monthly reports coming in, with all that information needed to be copied from them and inserted into rows in a master file in different sheets based on the report information. Hope that makes sense.

– Faraj
Nov 20 '18 at 7:46

What precisely is your question? Are you asking how to pass arguments?

– SJR
Nov 20 '18 at 7:47

1

Not sure I understand, but once you have defined named ranges in one sub, you will able to reference them in other subs automatically (as you can access them via the sheet directly). You might want to read this cpearson.com/excel/writingfunctionsinvba.aspx

– SJR
Nov 20 '18 at 7:56

2

@Libzz, turns out I won't be needing arguments, since this solution worked as well. But thank you for your effort anyway!

– Faraj
Nov 20 '18 at 12:09

|
show 12 more comments

Basically, what you are trying to do is copying and inserting the same kinds of data from one workbook to another. Is that right?

– libzz
Nov 20 '18 at 7:35

Hi @libzz! Yes, that is correct. To be exact, there will be several workbooks - around 10, but they will all contain the same kind of sheets/data, with all data in fixed locations, so the rows/sheet names will not change, only the values. It is basically monthly reports coming in, with all that information needed to be copied from them and inserted into rows in a master file in different sheets based on the report information. Hope that makes sense.

– Faraj
Nov 20 '18 at 7:46

What precisely is your question? Are you asking how to pass arguments?

– SJR
Nov 20 '18 at 7:47

1

Not sure I understand, but once you have defined named ranges in one sub, you will able to reference them in other subs automatically (as you can access them via the sheet directly). You might want to read this cpearson.com/excel/writingfunctionsinvba.aspx

– SJR
Nov 20 '18 at 7:56

2

@Libzz, turns out I won't be needing arguments, since this solution worked as well. But thank you for your effort anyway!

– Faraj
Nov 20 '18 at 12:09

Basically, what you are trying to do is copying and inserting the same kinds of data from one workbook to another. Is that right?

– libzz
Nov 20 '18 at 7:35

Hi @libzz! Yes, that is correct. To be exact, there will be several workbooks - around 10, but they will all contain the same kind of sheets/data, with all data in fixed locations, so the rows/sheet names will not change, only the values. It is basically monthly reports coming in, with all that information needed to be copied from them and inserted into rows in a master file in different sheets based on the report information. Hope that makes sense.

– Faraj
Nov 20 '18 at 7:46

What precisely is your question? Are you asking how to pass arguments?

– SJR
Nov 20 '18 at 7:47

Not sure I understand, but once you have defined named ranges in one sub, you will able to reference them in other subs automatically (as you can access them via the sheet directly). You might want to read this cpearson.com/excel/writingfunctionsinvba.aspx

– SJR
Nov 20 '18 at 7:56

@Libzz, turns out I won't be needing arguments, since this solution worked as well. But thank you for your effort anyway!

– Faraj
Nov 20 '18 at 12:09

|
show 12 more comments

2 Answers
2

active

oldest

votes

What you need are arguments (aka parameters).

e.g.

Sub CopyAndInsertStuff(sourceLocation as String, destinationLocation as String)



    Set wbSrc = Workbooks(sourceLocation)

    Set wbDst = Workbooks(destinationLocation)



    'Do your copying and inserting logic here...



End Sub

Then call that function by:

Call CopyAndInsertStuff("C:pathtosourceFile.xlsx", "C:pathtodestinationFile.xlsx")

answered Nov 20 '18 at 7:56

libzz

112112

Thanks for the tip, I will try that and see if it works. Might require some tinkering first.

– Faraj
Nov 20 '18 at 9:09

add a comment |

If you are looking at adding another 165 subs, may I suggest to have a look at loops and/or arrays?

It might take you over all about the same time to develop it (considering the learning curve), but the code will be about 150 times shorter (do everything in 1-2-3 subs), and much easier to maintain. This, and in conjunction with the suggested parameters to call similar functionality from other subs or functions, would be a lot more efficient.

Here are the first results from Google when it comes to loops and arrays, and after a quick look, they do cover the basic needs:

Loops: https://www.excel-easy.com/vba/loop.html

Arrays: https://www.excel-easy.com/vba/array.html

Final advice, keep in mind that the less you interact with the workbooks from VBA, the faster your macros will run. ie: load your full range in an array, perform the transforming you want, then put it back in the workbook - you are only accessing the workbook 2 times as needed. If on the other hand, you use vba to copy cell A to cell B, few tens/hundreds of thousands times... it will be slower.

answered Nov 20 '18 at 9:39

DarXyde

24016

Hi DarXyde! Thanks for the links! I have seen mentions of loops and arrays before, but I could not find a way so far to modify my code to include them. Once my code is complete and I am certain of what tasks are being performed, I will most definitely look into optimization and replacing long codes with something more sophisticated. Right now, that stuff just makes my brain go nuts, unfortunately..

– Faraj
Nov 20 '18 at 9:58

add a comment |

Your Answer

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: "1"
};
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',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53386523%2fexcel-vba-calling-referring-to-a-named-range-from-another-sub%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

What you need are arguments (aka parameters).

e.g.

Sub CopyAndInsertStuff(sourceLocation as String, destinationLocation as String)



    Set wbSrc = Workbooks(sourceLocation)

    Set wbDst = Workbooks(destinationLocation)



    'Do your copying and inserting logic here...



End Sub

Then call that function by:

Call CopyAndInsertStuff("C:pathtosourceFile.xlsx", "C:pathtodestinationFile.xlsx")

answered Nov 20 '18 at 7:56

libzz

112112

Thanks for the tip, I will try that and see if it works. Might require some tinkering first.

– Faraj
Nov 20 '18 at 9:09

add a comment |

What you need are arguments (aka parameters).

e.g.

Sub CopyAndInsertStuff(sourceLocation as String, destinationLocation as String)



    Set wbSrc = Workbooks(sourceLocation)

    Set wbDst = Workbooks(destinationLocation)



    'Do your copying and inserting logic here...



End Sub

Then call that function by:

Call CopyAndInsertStuff("C:pathtosourceFile.xlsx", "C:pathtodestinationFile.xlsx")

answered Nov 20 '18 at 7:56

libzz

112112

Thanks for the tip, I will try that and see if it works. Might require some tinkering first.

– Faraj
Nov 20 '18 at 9:09

add a comment |

What you need are arguments (aka parameters).

e.g.

Sub CopyAndInsertStuff(sourceLocation as String, destinationLocation as String)



    Set wbSrc = Workbooks(sourceLocation)

    Set wbDst = Workbooks(destinationLocation)



    'Do your copying and inserting logic here...



End Sub

Then call that function by:

Call CopyAndInsertStuff("C:pathtosourceFile.xlsx", "C:pathtodestinationFile.xlsx")

answered Nov 20 '18 at 7:56

libzz

112112

What you need are arguments (aka parameters).

e.g.

Sub CopyAndInsertStuff(sourceLocation as String, destinationLocation as String)



    Set wbSrc = Workbooks(sourceLocation)

    Set wbDst = Workbooks(destinationLocation)



    'Do your copying and inserting logic here...



End Sub

Then call that function by:

Call CopyAndInsertStuff("C:pathtosourceFile.xlsx", "C:pathtodestinationFile.xlsx")

answered Nov 20 '18 at 7:56

libzz

112112

answered Nov 20 '18 at 7:56

libzz

112112

answered Nov 20 '18 at 7:56

libzz

112112

answered Nov 20 '18 at 7:56

libzz

112112

Thanks for the tip, I will try that and see if it works. Might require some tinkering first.

– Faraj
Nov 20 '18 at 9:09

add a comment |

Thanks for the tip, I will try that and see if it works. Might require some tinkering first.

– Faraj
Nov 20 '18 at 9:09

Thanks for the tip, I will try that and see if it works. Might require some tinkering first.

– Faraj
Nov 20 '18 at 9:09

add a comment |

If you are looking at adding another 165 subs, may I suggest to have a look at loops and/or arrays?

Here are the first results from Google when it comes to loops and arrays, and after a quick look, they do cover the basic needs:

Loops: https://www.excel-easy.com/vba/loop.html

Arrays: https://www.excel-easy.com/vba/array.html

answered Nov 20 '18 at 9:39

DarXyde

24016

Hi DarXyde! Thanks for the links! I have seen mentions of loops and arrays before, but I could not find a way so far to modify my code to include them. Once my code is complete and I am certain of what tasks are being performed, I will most definitely look into optimization and replacing long codes with something more sophisticated. Right now, that stuff just makes my brain go nuts, unfortunately..

– Faraj
Nov 20 '18 at 9:58

add a comment |

If you are looking at adding another 165 subs, may I suggest to have a look at loops and/or arrays?

Here are the first results from Google when it comes to loops and arrays, and after a quick look, they do cover the basic needs:

Loops: https://www.excel-easy.com/vba/loop.html

Arrays: https://www.excel-easy.com/vba/array.html

answered Nov 20 '18 at 9:39

DarXyde

24016

Hi DarXyde! Thanks for the links! I have seen mentions of loops and arrays before, but I could not find a way so far to modify my code to include them. Once my code is complete and I am certain of what tasks are being performed, I will most definitely look into optimization and replacing long codes with something more sophisticated. Right now, that stuff just makes my brain go nuts, unfortunately..

– Faraj
Nov 20 '18 at 9:58

add a comment |

If you are looking at adding another 165 subs, may I suggest to have a look at loops and/or arrays?

Here are the first results from Google when it comes to loops and arrays, and after a quick look, they do cover the basic needs:

Loops: https://www.excel-easy.com/vba/loop.html

Arrays: https://www.excel-easy.com/vba/array.html

answered Nov 20 '18 at 9:39

DarXyde

24016

If you are looking at adding another 165 subs, may I suggest to have a look at loops and/or arrays?

Here are the first results from Google when it comes to loops and arrays, and after a quick look, they do cover the basic needs:

Loops: https://www.excel-easy.com/vba/loop.html

Arrays: https://www.excel-easy.com/vba/array.html

answered Nov 20 '18 at 9:39

DarXyde

24016

answered Nov 20 '18 at 9:39

DarXyde

24016

answered Nov 20 '18 at 9:39

DarXyde

24016

answered Nov 20 '18 at 9:39

DarXyde

24016

Hi DarXyde! Thanks for the links! I have seen mentions of loops and arrays before, but I could not find a way so far to modify my code to include them. Once my code is complete and I am certain of what tasks are being performed, I will most definitely look into optimization and replacing long codes with something more sophisticated. Right now, that stuff just makes my brain go nuts, unfortunately..

– Faraj
Nov 20 '18 at 9:58

add a comment |

Hi DarXyde! Thanks for the links! I have seen mentions of loops and arrays before, but I could not find a way so far to modify my code to include them. Once my code is complete and I am certain of what tasks are being performed, I will most definitely look into optimization and replacing long codes with something more sophisticated. Right now, that stuff just makes my brain go nuts, unfortunately..

– Faraj
Nov 20 '18 at 9:58

Hi DarXyde! Thanks for the links! I have seen mentions of loops and arrays before, but I could not find a way so far to modify my code to include them. Once my code is complete and I am certain of what tasks are being performed, I will most definitely look into optimization and replacing long codes with something more sophisticated. Right now, that stuff just makes my brain go nuts, unfortunately..

– Faraj
Nov 20 '18 at 9:58

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

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

Search This Blog

Ufyukyu