How to prompt user to define a named range

[Gingertrees]

I know how to import a sheet into a workbook, but what I don't know is how to prompt the user to define a named range based on said sheet.

(I tried to explain this earlier, but muddied it up with too many details.)

Each day, I copy a sheet into my workbook, that has a useful range that always starts on cell B4, and ends on cell C & end of column. (sometimes C30, others C200, etc.).

Ideas?

 

[mikerickson]

I'm not sure what you are asking.
Do you want the user to pick a range (their choice) and then use it to create a named range?

Or do you want to remind the user to do the chore of updating a named range based on the recently copied sheet?

If you want the B4:C(end of column) range to be particular named range, you could code to do that and not have to bother the user.

With Sheets(1)
    Range(.Cells(4,2), .Cells(Rows.Count, 3).End(xlup)).Name = "myNamedRange"
End With

 

[MARK858]

Why prompt the user and not just name the range? For example...

Sub getrange()
ActiveSheet.Range("B2:C" & ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row).Name = "MyRange"
End Sub

The above assumes column A is your longest column.

Edit: should have refreshed my screen and then would have seen Mikes post

 

[L. Howard]

Is it important that the user define the name, or could you just do it with a macro like this?

Option Explicit

Sub ThreeLineRange()
Dim OneRng As Range
  Set OneRng = Range("B4:C" & Cells(Rows.Count, "B").End(xlUp).Row)
  ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=OneRng
End Sub

Or if you want to prompt the user, maybe with a change event macro.
Needs a change in cell F1 to fire the macro and show the message.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> Range("F1") Then Exit Sub

MsgBox "Now is the time for all" _
        & vbCr & "good men to define a" _
        & vbCr & vbCr & "Named Range"
End Sub

Howard


Edit: Me too.

 

[Gingertrees]

OK, thanks for all the replies!

I think I see that this can be done automatically - yea!

One question:
As soon as I run the import routine to pull in the new workbook, I'll have it do the ActiveWorkbook.Names thing from above. This won't cause issue if that name is already in existence? (This workbook gets quite large, since sheets are pulled in daily, and will all have a "MyRange", as L Howard listed.)

 

[L. Howard]

OK, thanks for all the replies!

I think I see that this can be done automatically - yea!

One question:
As soon as I run the import routine to pull in the new workbook, I'll have it do the ActiveWorkbook.Names thing from above. This won't cause issue if that name is already in existence? (This workbook gets quite large, since sheets are pulled in daily, and will all have a "MyRange", as L Howard listed.)

If you run any of the codes and the name already exists, you are only changing the cells the name refers to. Name remains the same.

You can test this by running a code, then Go to the Formulas tab > Name Manager > Click on the named range in the window (highlight it) > Edit

See the RefersTo: window showing the range of cells the name is referring to. OK > out of all that and change the data on the sheet and re-run the naming macro and recheck the Refers To: window as above. Note the range is different and the name is the same.

Howard