error with macro to name new worksheet with cell from old worksheet

Hi all,
I seem to be having with my simple code below. I'm creating a new
worksheet with range from old worksheet and want to rename the new
worksheet with a cell value from old worksheet but am getting an error.
can anyone help?

For Each sh In ThisWorkbook.Worksheets
'Bold section headings
sh.Range("A2,A4").Font.Bold = True


'Make second worksheet

Set Newsh = ThisWorkbook.Worksheets.Add


sh.Range("A23:G95").Cut _
Destination:=Newsh.Range("a1")


sh.Range("A:g").Copy

Newsh.Range("a:g").PasteSpecial Paste:=xlPasteFormats
Newsh.Name = sh.Range("A2").Value (ERROR HERE)

before line:

Newsh.Name = sh.Range("A2").Value (ERROR HERE)


put this :
msgbox (sh.Range("A2").Value)
exit sub

then try to look at the msgbox content - maybe it will give u any idea

Hi,
Thanks.. I'm actually getting a run-time error '1004'
Application-defined or object defined error. Nothing comes up in the
msgbox though.

It works the first time around because that is may be a value in A2,but it
there is no value(blank or empty) in cell A2 of the next worksheet you will
get a run-time 1004 error. Make sure there is a value in cell A2 of the new
sheet.

"Moon" wrote:

> Hi all,
> I seem to be having with my simple code below. I'm creating a new
> worksheet with range from old worksheet and want to rename the new
> worksheet with a cell value from old worksheet but am getting an error.
> can anyone help?
>
> For Each sh In ThisWorkbook.Worksheets
> 'Bold section headings
> sh.Range("A2,A4").Font.Bold = True
>
>
> 'Make second worksheet
>
> Set Newsh = ThisWorkbook.Worksheets.Add
>
>
> sh.Range("A23:G95").Cut _
> Destination:=Newsh.Range("a1")
>
>
> sh.Range("A:g").Copy
>
> Newsh.Range("a:g").PasteSpecial Paste:=xlPasteFormats
> Newsh.Name = sh.Range("A2").Value (ERROR HERE)
>
>

ensure that new sheets ( added by procedure ) have different names coz ur
code works on my comp as far as the names of new sheets are different