Worksheet copy with rename
Bob,
I don't know what's going on. On one computer, both of my versions work fine.
Here, it remains as I explained. GS's solution 1 does work but I am not sure why.
Thanks for suggesting I check for protection. No protection was on the sheet or
the workbook. Still wondering but have a working program. Must test on a few
other machines to be sure.
Thanks again.
"Bob Phillips" wrote in message ...
It all seems good, and it works fine for me.
You don't have protection or anything else unusual?
--
HTH
Bob Phillips
(remove nothere from the email address if mailing direct)
"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
|