Using VBA to name ranges

[snipescc]

Hi folks. I'm trying to use VBA to name ranges, because I will have a lot of them to do (one for each client). If doing this so on a time sheet, an employee can pick the client from a drop down menu, and the next drop-down menu will give codes unique to that client. I'm using Indirect and named ranges to accomplish that. The trouble I'm running into is writing VBA code that will help me make the ranges, since there may be a couple hundred of them and I don't want to do them manually. This is what the document looks like.

Smith	Jones
96	100
97	101
98	102
99	103

<tbody>
</tbody>

I recorded a macro with relative references, that took cell A1 (Smith), copied it, highlighted A2:A5, and then copied the 'Smith' into the place to name ranges. After that, I clicked on B2 (Jones). Here is my code:

Sub Ranges()'
' Ranges Macro
'
' Keyboard Shortcut: Ctrl+q
'
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1:A4").Select
Application.Goto Reference:="Smith"
ActiveCell.Offset(-1, 1).Range("A1").Select
End Sub

What I would like is for the code to do is copy a cell's content, then highlight the 4 cells under it, and name them whatever is in the top cell. When it has done that, I'd like it to go to the top cell of the next column. Can anyone help me? thanks.

 

[VBA Geek]

if your data starts in A1, try

Range("A1").CurrentRegion.CreateNames True, False, False, False

 

[Rick Rothstein]

if your data starts in A1, try

Range("A1").CurrentRegion.CreateNames True, False, False, False

Hmm, a new one for me... I was not aware of the CreateNames method before.

 

[VBA Geek]

Thanks!
It mimics the Create From Selection button in the FORMULA tab which can be quite handy at times

 

[snipescc]

Is that the only code i use, and if not, which line do I replace with it? (I'm still pretty new to VBA programming)

 

[VBA Geek]

this one line should do all the work if your data starts in A1

 

[snipescc]

awesome! Thanks!