Mixing
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
excel vba excel-vba
asked Nov 20 '18 at 5:03
FarajFaraj
74
|
show 12 more comments
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
excel vba excel-vba
asked Nov 20 '18 at 5:03
FarajFaraj
74
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!
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
excel vba excel-vba
asked Nov 20 '18 at 5:03
FarajFaraj
74
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
excel vba excel-vba
excel vba excel-vba
asked Nov 20 '18 at 5:03
FarajFaraj
74
asked Nov 20 '18 at 5:03
FarajFaraj
74
edited Nov 20 '18 at 9:53
Faraj
asked Nov 20 '18 at 5:03
FarajFaraj
74
asked Nov 20 '18 at 5:03
FarajFaraj
74
asked Nov 20 '18 at 5:03
FarajFaraj
74
74
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
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
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
What precisely is your question? Are you asking how to pass arguments?
– SJR
Nov 20 '18 at 7:47
1
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
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
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
@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
libzzlibzz
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
DarXydeDarXyde
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
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
libzzlibzz
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
libzzlibzz
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
libzzlibzz
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
libzzlibzz
112112
answered Nov 20 '18 at 7:56
libzzlibzz
112112
answered Nov 20 '18 at 7:56
libzzlibzz
112112
answered Nov 20 '18 at 7:56
libzzlibzz
112112
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
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
DarXydeDarXyde
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?
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
DarXydeDarXyde
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?
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
DarXydeDarXyde
24016
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
DarXydeDarXyde
24016
answered Nov 20 '18 at 9:39
DarXydeDarXyde
24016
answered Nov 20 '18 at 9:39
DarXydeDarXyde
24016
answered Nov 20 '18 at 9:39
DarXydeDarXyde
24016
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
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 |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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