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

[Moon]

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)

 

[Moon]

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

 

[Snake Plissken]

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

 

[Guest]

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.

 

[Snake Plissken]

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