View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Doug Broad[_4_] Doug Broad[_4_] is offline
external usenet poster
 
Posts: 6
Default Worksheet copy with rename

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