Is there any way to create and name new worksheets using names in an
existing database? or having created the sheets - to rename them in bulk??
TIA
Is there any way to create and name new worksheets using names in an
existing database? or having created the sheets - to rename them in bulk??
TIA
Hi Harry
Try this one with the list in Range("A2:A100") of "Sheet1"
Sub test()
Dim cell As Range
Dim WSNew As Worksheet
For Each cell In Sheets("Sheet1").Range("A2:A100").SpecialCells(xlCellTypeConstants)
Set WSNew = Worksheets.Add
On Error Resume Next
WSNew.Name = cell.Value
If Err.Number > 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
Next cell
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Harry Limey" <harrylimey(at)Lycos.co.uk> wrote in message news:%[email protected]...
> Is there any way to create and name new worksheets using names in an
> existing database? or having created the sheets - to rename them in bulk??
> TIA
>
>
Ron
Works perfectly - did you just write that or have it on file??
either way, I will be using that again.
Many thanks
Harry
"Ron de Bruin" <[email protected]> wrote in message
news:[email protected]...
> Hi Harry
>
> Try this one with the list in Range("A2:A100") of "Sheet1"
>
> Sub test()
> Dim cell As Range
> Dim WSNew As Worksheet
>
> For Each cell In
Sheets("Sheet1").Range("A2:A100").SpecialCells(xlCellTypeConstants)
> Set WSNew = Worksheets.Add
> On Error Resume Next
> WSNew.Name = cell.Value
> If Err.Number > 0 Then
> MsgBox "Change the name of : " & WSNew.Name & " manually"
> Err.Clear
> End If
> On Error GoTo 0
>
> Next cell
> End Sub
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
Just write it, only the error check I use more
Glad you can use it
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Harry Limey" <harrylimey(at)Lycos.co.uk> wrote in message news:[email protected]...
> Ron
>
> Works perfectly - did you just write that or have it on file??
> either way, I will be using that again.
> Many thanks
>
> Harry
>
> "Ron de Bruin" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Harry
>>
>> Try this one with the list in Range("A2:A100") of "Sheet1"
>>
>> Sub test()
>> Dim cell As Range
>> Dim WSNew As Worksheet
>>
>> For Each cell In
> Sheets("Sheet1").Range("A2:A100").SpecialCells(xlCellTypeConstants)
>> Set WSNew = Worksheets.Add
>> On Error Resume Next
>> WSNew.Name = cell.Value
>> If Err.Number > 0 Then
>> MsgBox "Change the name of : " & WSNew.Name & " manually"
>> Err.Clear
>> End If
>> On Error GoTo 0
>>
>> Next cell
>> End Sub
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks