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.

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

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)