Thanks George. Must be something with my computer or version of Excel because
I can run all these programs at home and get good results but on this computer
yours copies the sheet but doesn't rename.

Appreciate your time.

"George Nicholson" wrote in message ...
Worksheets.Add adds a new, blank worksheet
Worksheet(sht1).Copy will create a copy of sht1

Maybe something like:
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Set sht1 = ActiveSheet
sht1.Copy After:=sht1
Set sht2 = Worksheets(sht1.index + 1)
sht2.Name = sht1.Range("J1").Value

Otherwise, if you really want to use PasteSpecial, explicitly activate sht2
just before PasteSpecial. The Help entry for PasteSpecial says: "You must
select the destination before using this method." I haven't found that to be
entirely true, but you *do* have to make sure that at least the destination
*sheet* is Activated or Selected (one of the few times either are actually
necessary). sht2 *should* automatically be active right after the Add, but
better to be explicit if things aren't working as you expect. I believe the
following should work:

sht1.UsedRange.Copy
sht2.Activate
sht2.Range("A1").PasteSpecial (xlPasteAll)

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"Doug Broad" wrote in message
...
Private Sub CommandButton1_Click()
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Set sht1 = ActiveSheet
Set sht2 = Worksheets.Add(after:=sht1)
sht2.Name = sht1.Range("J1").Value
sht1.UsedRange.Copy
sht2.Range("A1").PasteSpecial (xlPasteAll)


End Sub

The above code is supposed to:
1. create a new worksheet after the current worksheet containing all
the information in the current worksheet and
2. To renme the worksheet to the contents of J1 in the first
worksheet.

The problem:
Depending on the order of the statements, it either creates the new
worksheet and renames it correctly OR it creates the worksheet
and correctly copies the info. I can't get it to do both.

What am I doing wrong? Is there a better way? Thanks