Code to name numerous ranges
Hi Howard,
Am Mon, 26 May 2014 00:32:51 -0700 (PDT) schrieb L. Howard:
Is there a way to only include the cells in columns D, F, H, J, L instead of D through L?
sorry, I have to clean my glasses
Try:
Sub MyNameCoder()
Dim j As String, strSh As String
Dim arrJ As Variant, ArrSh As Variant
Dim i As Long, n As Long
j = "Mon,Tue,Wed,Thu,Fri"
arrJ = Split(j, ",")
strSh = "Sheet1,Sheet2,Sheet3,Sheet4,Sheet5"
ArrSh = Split(strSh, ",")
For n = LBound(ArrSh) To UBound(ArrSh)
With Sheets(ArrSh(n))
For i = 3 To 100
.Names.Add Name:=arrJ(n) & "_" & i, _
RefersTo:=Application.Union(.Cells(i, "D"), .Cells(i, "F"),
..Cells(i, "H"), _
.Cells(i, "J"), .Cells(i, "L"))
Next
End With
Next
End Sub
Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
|