Capture Cell range from user input in Excel VBA
I receive Excel workbooks from various clients containing lists of employees with full and date of birth. They come in different formats. I am creating an Excel spreadsheet that I will add to each of these workbooks that will allow me to apply a series of formulas to customer data. The problem is, I don't know the location of the date of birth in advance.
I want a macro to prompt me to select a range for the date of birth and then put that data range in cell (F31) in my worksheet so that I can pull it into other formulas.
The code generated below works, but it doesn't pull out the worksheet tab name along with the range. How can I get the name of the worksheet along with the range of cells?
Sub ChooseDOBRange()
Dim rng As Range
Set rng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
rng.Copy
Worksheets("COVER SHEET").Range("F31") = rng.Address
End Sub
source to share
How can I get the name of the worksheet along with the range of cells?
You need rng.Parent.Name
Is this what you are trying?
Sub ChooseDOBRange()
Dim rng As Range
On Error Resume Next '<~~ In case user presses Cancel
Set rng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
On Error GoTo 0
If Not rng Is Nothing Then
rng.Copy
Worksheets("COVER SHEET").Range("F31").Value = _
rng.Parent.Name & " - " & rng.Address '<~~ Something like "Sheet2 - $C$6:$H$14"
End If
End Sub
source to share
There are two ways to get this, depending on whether you want the name Workbook
in the formula. I am testing them in the Immediate window.
Method 1 uses .Address
with the parameter External
set toTrue
?ActiveCell.Address(,,,True)
[Book2] Sheet1! $ A $ 1
Method 2 uses .Address
along with the name of the sheet Range.Parent.Name
where Parent
relates to Worksheet
forRange
?"'" & ActiveCell.Parent.Name & "'!" & ActiveCell.Address
'Sheet1'! $ A $ 1
source to share