Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet copy with rename
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet copy with rename
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet copy with rename
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet copy with rename
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet copy with rename
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. "GS" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro: Copy a worksheet and rename it to a specified value | Excel Discussion (Misc queries) | |||
Copy worksheet and rename sheet | Excel Discussion (Misc queries) | |||
Copy/Paste Worksheet to last and rename with input box for value. | Excel Programming | |||
Copy/Paste Worksheet to last and rename with input box for value. | Excel Programming | |||
Copy worksheet from multiple files in one DIR to another DIR & rename | Excel Programming |