Calling/Referring to a named Range from another Sub

Question

I managed to write a code from different threads and code examples from around the web. It is trial and error and lots of copy-pasting.

I have several ranges defined within my subs:

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 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, I would like to put these Ranges in a separate Sub and call on it in each new Sub.

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. 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:\Users\user\Folder\File.xlsx")
Set Y = ThisWorkbook

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:\Users\user\Folder\File.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

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
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
What precisely is your question? Are you asking how to pass arguments? — SJR
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
@Libzz, turns out I won't be needing arguments, since this solution worked as well. But thank you for your effort anyway! — Faraj

libzz libzz · Accepted Answer · 2018-11-20T07:56:54

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:\path\to\source\File.xlsx", "C:\path\to\destination\File.xlsx")

Calling/Referring to a named Range from another Sub

2 Answers