hindersaliva
asked on
Excel VBA - setting scope of Named Range to Worksheet problem
This works, but the Named Range has a 'Workbook' scope, even though the range is qualified to ActiveSheet. I'm stuck because the macro recordser version uses RefersTo. This is simper.
How can I make it 'Worksheet' scope (using this syntax version, if possible) please?
How can I make it 'Worksheet' scope (using this syntax version, if possible) please?
Private Sub btnOk_Click()
Dim strSourceRange As String
Dim intOffsetColumns As Integer
Dim wksActiveWorksheet As Worksheet
Dim rngSourceRange As Range
strSourceRange = Me.refSourceRange.Value
Set wksActiveWorksheet = ActiveSheet
Set rngSourceRange = wksActiveWorksheet.Range(strSourceRange)
intOffsetColumns = Me.txtOffsetColumns
iCol = 1
For iRow = 1 To rngSourceRange.Rows.Count - 1
strName = "TRAC_" + rngSourceRange.Cells(iRow, 1).Value
rngSourceRange.Cells(iRow, 1).Offset(0, intOffsetColumns).Name = strName
Next iRow
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Genius! Thanks Neeraj.
ASKER
https://www.experts-exchan
The solution I got from there was the other syntax and it worked. But not the 'worksheet' scope I wanted.
The cope will be in an AddIn and will run on the ActiveWorkbook.ActiveSheet