How to use macro to create a name ?

[rickyckc]

Hi all,

I have a workbook call Sales. In this workbook, I have a sheet call Sales Staff. In this sheet, I have a column of sales person's names. This list of sales names are grab from a database and is always not the same amount of people. I need a macro to do variable selection of the sales staff names, in column C, to help create a name call SalesStaff.

Currently what I have is manual selection...

[/code]
ActiveWorkbook.Names.Add Name:="SalesStaff", RefersToR1C1:= _
"='Sales Staff'!R2C3:R7C3"

Thanks.

Best Regards,
Ricky

 

[Richard Schollar]

Hi Ricky

I assume you want the name to expand to the size of the data? Perhaps then:

ActiveWorkbook.Names.Add Name:="SalesStaff", RefersToR1C1:= _ 
"='Sales Staff'!R2C3:R" & Sheets("Sales Staff").Cells(Rows.Count,"C").End(xlUp).Row & "C3"

 

[rickyckc]

Hi Richard,

Thanks. I got it figured myself and got it working. What I need now is a line to check whether a name called 'SalesStaff' exist. If exist, delete it. If does not exist, do nothing and carry on with next macro line.

Likewise a line to check whether a sheet call Sales Order exist. If exist, do nothing. If does not exist, create it.

Thanks.

Best Regards,
Ricky

 

[Richard Schollar]

Maybe:

On Error Resume Next
Names("MyName").Delete
On Error Goto 0

'worksheet stuff

On Error Resume Next
Err.Clear
Set ws = Worksheets("Sales Order")
If Err <> 0 Then
   Set ws = Worksheets.Add
   ws.Name = "Sales Order"
End If
On Error GoTo 0