Capture Cell range from user input in Excel VBA

Question

I receive excel workbooks from various customers containing lists of employees with gender and date of birth columns. They arrive in varying formats. I am creating an excel worksheet that I will add to each of these workbooks which will allow me to apply a series of formulas to the customer data. The problem is that I do not know the location of the date of birth data in advance.

I want a macro to prompt me to select the range for the date of birth values and then place that data range into a cell (F31) on my worksheet so that I can pull it into my other formulas.

The code I created below works, but it does not pull the worksheet tab name along with the range. How can I get the worksheet name along with the cell range?

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

Siddharth Rout Siddharth Rout · Accepted Answer · 2015-06-23T21:26:25

How can I get the worksheet name along with the cell range?

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

Capture Cell range from user input in Excel VBA

2 Answers