VBA Range("") .Select Reference Name instead of Cells (Office 365)

[jrjobe]

Is there a way to change the code below to select a range of named cells instead of a range of cells to accomplish the same thing?

Sub EMTS_RT_1_1l2(control As IRibbonControl)
'
' EMTS_RT_1-1/2 Macro
'
Windows("DATABASE.XLSM").Activate
'       EMT    COUP    CONN      EL     support    label
Range("A8:G8,A30:G30,A74:G74,A108:G108,A702:G702,A715:G715").Select
Selection.COPY
Application.Run "BACK"

' extension formulas
ActiveCell.Offset(0, 0).Range("a1:G1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = "=+RC[-2]*RC[-1]"
Selection.COPY
ActiveCell.Offset(1, 0).Range("A1:A5").Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 2).Select
ActiveCell.FormulaR1C1 = "=+RC[-4]*RC[-1]"
Selection.COPY
ActiveCell.Offset(1, 0).Range("A1:A5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
' qty formulas
ActiveCell.Offset(0, -4).Select
ActiveCell.FormulaR1C1 = "=+R[-1]c*0.1"
ActiveCell.Offset(3, 0).Select
ActiveCell.FormulaR1C1 = "=@round((+R[-4]C/8),0)"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=@round((+R[-5]C/25),0)"
ActiveCell.Offset(1, -1).Select

End Sub

I have 2 workbooks, one estimate.xlsm and one database.xlsm. On the estimate workbook, I have a customUI toolbar menu (each menu item points to the appropriate macro within database.xlsm) with options the estimator can select from to generate a quote based on common items that get quoted together. In the example code above, if the user selects 1 1/2 EMT Steel RT from the menu (in the estimate workbook), it will copy the range of cells from the database LIST worksheet, over to the estimate worksheet. The problem with the above, if items are needed to be added to the database, I have to adjust a lot of the code in the VBA, which makes it very time consuming.

I am wondering if I can select the same range of cells in the database and name them to pull from instead? For example, the range of cells would instead be named something like 1_1_2_EMTS_RT. This way, it would seem that it may not matter where I add items in the database as long as the names are updated appropriately. It sure would save a ton of time doing it this way.

Of note, there are no formulas on either worksheet in both workbooks, as the formulas used are contained within the VBA and added when a menu item is added.

Any other suggested improvements would be greatly appreciated.

Here's a mini-sheet of the estimate that shows how the items in the above example is copied to it when selected from the menu. In this example, the estimator will put in the quantity (in feet) for the EMT in cell B4, with cells B5, B8, and B9 containing a formula from the macro that will calculate the appropriate quantity needed. The other two cells vary, so the estimator will input the quantity manually.

BLANK ESTIMATE.xlsm
	A	B	C	D	E	F	G
3	Conduit	Qty.	Material	Extension	Labor	Extension	SORT CODE
4	1 1/2" EMT	1,000	4.493	4,493.20	0.054	54.00	5
5	1 1/2" EMT STL. COUP R.T.	100	4.7603	476.03	-	-	25
6	1 1/2" EMT STL. CONN R.T.	-	5.6446	-	-	-	65
7	1 1/2" EMT 90	-	22.954	-	0.800	-	95
8	1 1/2" SUPPORT	125	2.100	262.50	-	-	634
9	1 1/2" LABEL	40	0.250	10.00	0.020	0.80	646
10		-	-	-	-	-
BASE

 

[Joe4]

Is there a way to change the code below to select a range of named cells instead of a range of cells to accomplish the same thing?

Not sure if this helps, but you cannot a bunch of named ranges into one single larger range using the Union function.
Here is what that looks like on your named ranges:

    Dim nRngs()
    Dim i As Long
    Dim rng As Range
    
'   Create array of named ranges
    nRngs = Array("EMT", "COUP", "CONN", "EL", "support", "label")
    
'   Combine all named ranges into one big range named "rng"
    For i = LBound(nRngs) To UBound(nRngs)
        If i = 0 Then
            Set rng = Range(nRngs(i))
        Else
            Set rng = Union(rng, Range(nRngs(i)))
        End If
    Next i

So once complete, all the cells in all of your individual named ranges would be found in the range named "rng" that we created in VBA.

 

[jrjobe]

@Joe4 thanks a ton for this info! I took a look at it, but I don't think it will work the way I need it to because we have well over 3 or 4 dozen entries that follow the same pattern as the EMTS_RT_1_1l2 example that I posted (multiple types: GRC, PVC, etc. and sizes up to 6 inches). However, your reply got me thinking and this is what I did, and it worked:

Using the example I posted above, in the database.xlsm LIST worksheet, I selected each of the cells contained within that range, created a name for those selected cells, called EMTS_RT_1_1l2, using the Name Manager. In the VBA code I posted, I replaced the cell ranges with "EMTS_RT_1_1l2", so when I ran the macro, it pulled it over to the estimate worksheet, just as it did before. This makes it much easier as now instead of going through to update each macro range within the VBA editor, I can simply create a name for the same cells and use that instead. The only thing I might have to manually update if there are significant changes to the LIST worksheet is the formulaR1C1 property, but I think, for the most part, this should not happen very often. If anything needs to be added, I'm having the guys just add it to be very bottom of the worksheet after the last item, so they can send it back to me to update the stuff behind the scenes.

These files are being updated from 2003 that someone else created back in the late 90s and updated for Excel 2003, along with a toolbar. So I am cleaning up a bunch of code that is no longer needed and created a customUI Ribbon Tab for the latest versions of Excel, which fortunately is tied to the files.

Here's what it looks like:

Sub EMTS_RT_1_1l2(control As IRibbonControl)
'
' EMTS_RT_1-1/2 Macro
'
Windows("DATABASE.XLSM").Activate

Range("EMTS_RT_1_1l2").Select
Selection.COPY
Application.Run "BACK"

ActiveCell.Offset(0, 0).Range("a1:G1").Select
ActiveSheet.Paste

'Column D formula:
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = "=+RC[-2]*RC[-1]"
Selection.COPY
ActiveCell.Offset(1, 0).Range("A1:A5").Select
ActiveSheet.Paste

'Column F formula:
ActiveCell.Offset(-1, 2).Select
ActiveCell.FormulaR1C1 = "=+RC[-4]*RC[-1]"
Selection.COPY
ActiveCell.Offset(1, 0).Range("A1:A5").Select
ActiveSheet.Paste

Application.CutCopyMode = False

'Column B formulas for the support and label items
ActiveCell.Offset(0, -4).Select
ActiveCell.FormulaR1C1 = "=+R[-1]c*0.1"
ActiveCell.Offset(3, 0).Select
ActiveCell.FormulaR1C1 = "=@round((+R[-4]C/8),0)"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=@round((+R[-5]C/25),0)"
ActiveCell.Offset(1, -1).Select

End Sub

Thanks again for the reply as it did help nudge me in the right direction!

 

[Joe4]

You are welcome.
Glad I at least sparked an idea in your mind that got you to come up with a solution you can use!