Worksheet copy with rename

[Doug Broad]

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

 

[Bob Phillips]

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)

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

 

[George Nicholson]

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,

 

[GS]

Hi Doug,

You could do this a couple different ways, depending on whether you want a
copy of the first sheet, or if you want a new sheet with the data from the
first sheet copied onto it.

In either case, the sheet is renamed how you wanted. Here's a couple you can
try:


Sub CopyAndName()
' This creates a new sheet,
' copies the data from the first sheet,
' and renames it to a specified cell's value.

Dim sName As String, sht1 As Worksheet, sht2 As Worksheet

Set sht1 = ActiveSheet
sName = sht1.Range("A1").Value 'change the reference to your location
Set sht2 = Worksheets.Add(after:=ActiveSheet)
sht2.Name = sName
sht1.UsedRange.Copy Destination:=sht2.Range("A1")

End Sub



Sub CopyAndName2()
' This makes a copy of the ActiveSheet,
' and renames it to a specified cell's value

ActiveSheet.Copy after:=ActiveSheet

'At this point, it's the ActiveSheet,
'and contains the cell holding its name.
ActiveSheet.Name = Range("A1").Value 'change the reference to your
location

End Sub

Regards,
GS

 

[Doug Broad]

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.

 

[Doug Broad]

Hi George,
Thanks. At home both your functions and both mine work. Here,
your first one works fine and your second one does not rename.
Have tried it on a number of worksheets, none protected.
Thanks again. Still curious why it works on one machine and not
the other.

Thanks for your time.

 

[Doug Broad]

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.