| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Copying a worksheet into the same workbook
Hi, and thanks in advane for the help!
I would like to know how to copy an existing worksheet and automatically rename it from a cell within that sheet into the same workbook. I have searched but not found what I want to do. I think it has to do with the newsheet event but not sure how to do it. Mike |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Copying a worksheet into the same workbook
Try this for the activesheet with the sheet name in B3
Sub test() ActiveSheet.Copy after:=Sheets(Sheets.Count) On Error Resume Next ActiveSheet.Name = Range("B3").Value On Error GoTo 0 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Michael" wrote in message ... Hi, and thanks in advane for the help! I would like to know how to copy an existing worksheet and automatically rename it from a cell within that sheet into the same workbook. I have searched but not found what I want to do. I think it has to do with the newsheet event but not sure how to do it. Mike |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Copying a worksheet into the same workbook
Thanks so much Ron! It worked great!
I found that I only want to copy a range of cells into the new worksheet because I don't want the macro buttons copied into the new sheet. Any suggestions? Mike "Ron de Bruin" wrote: Try this for the activesheet with the sheet name in B3 Sub test() ActiveSheet.Copy after:=Sheets(Sheets.Count) On Error Resume Next ActiveSheet.Name = Range("B3").Value On Error GoTo 0 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Michael" wrote in message ... Hi, and thanks in advane for the help! I would like to know how to copy an existing worksheet and automatically rename it from a cell within that sheet into the same workbook. I have searched but not found what I want to do. I think it has to do with the newsheet event but not sure how to do it. Mike |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Copying a worksheet into the same workbook
Two ways
This also delete all shapes on the new sheet Sub test() ActiveSheet.Copy after:=Sheets(Sheets.Count) On Error Resume Next ActiveSheet.Name = Range("B3").Value ActiveSheet.DrawingObjects.Visible = True ActiveSheet.DrawingObjects.Delete On Error GoTo 0 End Sub Or copy a range into a new worksheet Sub test2() Dim ws1 As Worksheet Dim ws2 As Worksheet Set ws1 = ActiveSheet Set ws2 = Worksheets.Add(after:=Sheets(Sheets.Count)) On Error Resume Next ActiveSheet.Name = ws1.Range("B3").Value On Error GoTo 0 ws1.Range("A1:G20").Copy ws2.Range("A1") Set ws1 = Nothing Set ws2 = Nothing End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Michael" wrote in message ... Thanks so much Ron! It worked great! I found that I only want to copy a range of cells into the new worksheet because I don't want the macro buttons copied into the new sheet. Any suggestions? Mike "Ron de Bruin" wrote: Try this for the activesheet with the sheet name in B3 Sub test() ActiveSheet.Copy after:=Sheets(Sheets.Count) On Error Resume Next ActiveSheet.Name = Range("B3").Value On Error GoTo 0 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Michael" wrote in message ... Hi, and thanks in advane for the help! I would like to know how to copy an existing worksheet and automatically rename it from a cell within that sheet into the same workbook. I have searched but not found what I want to do. I think it has to do with the newsheet event but not sure how to do it. Mike |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Copying a worksheet into the same workbook
ActiveSheet.Name = ws1.Range("B3").Value
Use this instead ws2.Name = ws1.Range("B3").Value -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Two ways This also delete all shapes on the new sheet Sub test() ActiveSheet.Copy after:=Sheets(Sheets.Count) On Error Resume Next ActiveSheet.Name = Range("B3").Value ActiveSheet.DrawingObjects.Visible = True ActiveSheet.DrawingObjects.Delete On Error GoTo 0 End Sub Or copy a range into a new worksheet Sub test2() Dim ws1 As Worksheet Dim ws2 As Worksheet Set ws1 = ActiveSheet Set ws2 = Worksheets.Add(after:=Sheets(Sheets.Count)) On Error Resume Next ActiveSheet.Name = ws1.Range("B3").Value On Error GoTo 0 ws1.Range("A1:G20").Copy ws2.Range("A1") Set ws1 = Nothing Set ws2 = Nothing End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Michael" wrote in message ... Thanks so much Ron! It worked great! I found that I only want to copy a range of cells into the new worksheet because I don't want the macro buttons copied into the new sheet. Any suggestions? Mike "Ron de Bruin" wrote: Try this for the activesheet with the sheet name in B3 Sub test() ActiveSheet.Copy after:=Sheets(Sheets.Count) On Error Resume Next ActiveSheet.Name = Range("B3").Value On Error GoTo 0 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Michael" wrote in message ... Hi, and thanks in advane for the help! I would like to know how to copy an existing worksheet and automatically rename it from a cell within that sheet into the same workbook. I have searched but not found what I want to do. I think it has to do with the newsheet event but not sure how to do it. Mike |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Copying a worksheet into the same workbook
All has worked great so far, many thanks.
One last question (I hope): How do I get the new sheet not to display the gridlines automatically? The sheet it is copied from has the gridlines turned off. Mike "Ron de Bruin" wrote: ActiveSheet.Name = ws1.Range("B3").Value Use this instead ws2.Name = ws1.Range("B3").Value -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Two ways This also delete all shapes on the new sheet Sub test() ActiveSheet.Copy after:=Sheets(Sheets.Count) On Error Resume Next ActiveSheet.Name = Range("B3").Value ActiveSheet.DrawingObjects.Visible = True ActiveSheet.DrawingObjects.Delete On Error GoTo 0 End Sub Or copy a range into a new worksheet Sub test2() Dim ws1 As Worksheet Dim ws2 As Worksheet Set ws1 = ActiveSheet Set ws2 = Worksheets.Add(after:=Sheets(Sheets.Count)) On Error Resume Next ActiveSheet.Name = ws1.Range("B3").Value On Error GoTo 0 ws1.Range("A1:G20").Copy ws2.Range("A1") Set ws1 = Nothing Set ws2 = Nothing End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Michael" wrote in message ... Thanks so much Ron! It worked great! I found that I only want to copy a range of cells into the new worksheet because I don't want the macro buttons copied into the new sheet. Any suggestions? Mike "Ron de Bruin" wrote: Try this for the activesheet with the sheet name in B3 Sub test() ActiveSheet.Copy after:=Sheets(Sheets.Count) On Error Resume Next ActiveSheet.Name = Range("B3").Value On Error GoTo 0 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Michael" wrote in message ... Hi, and thanks in advane for the help! I would like to know how to copy an existing worksheet and automatically rename it from a cell within that sheet into the same workbook. I have searched but not found what I want to do. I think it has to do with the newsheet event but not sure how to do it. Mike |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro or OLE method to insert worksheet from template workbook | Excel Discussion (Misc queries) | |||
| Protect Workbook vs Worksheet?? | Excel Worksheet Functions | |||
| sort worksheet without affecting workbook | Excel Worksheet Functions | |||
| Linking cells in a worksheet to other worksheets in a workbook | Excel Discussion (Misc queries) | |||
| Error Copying Worksheet | Excel Worksheet Functions | |||
Hybrid Mode
