Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a copy of a worksheet
Hi guys, any help here greatly appreciated.
I have an excel template file called Notes.xlt It has 2 worksheets in it called 'TOC' and 'TEMPLATE' In 'TOC', I will only be using column A, starting at Cell A1 and working down column A. What I would like to achieve is the following: Worksheet 'TOC' A 1 This is cell A1 2 cell A2 3 cell A3 Through some sort of macro, I would like to be able to enter in text into a cell in column A, then according to the row number eg.'2', copy the TEMPLATE worksheet to a new worksheet, and rename it to that row number, so the name of the new worksheet becomes '2'. If I accidentally try and create the new worksheet that exists with this name, to prompt me to give me the option of recreating it, or cancel. Is this possible in excel? Any help is really appreciated. -- --------------------------------------------------------------------- "Are you still wasting your time with spam?... There is a solution!" Protected by GIANT Company's Spam Inspector The most powerful anti-spam software available. http://mail.spaminspector.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a copy of a worksheet
One way:
Put this in the worksheet code module of sheet TOC: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim wkSht As Worksheet Dim result As Integer Dim sName As String With Target If .Count 1 Then Exit Sub sName = .Row End With On Error Resume Next Set wkSht = Worksheets(sName) On Error GoTo 0 If Not wkSht Is Nothing Then result = MsgBox( _ Prompt:="Delete current sheet " & sName & "?", _ Buttons:=vbYesNo) If result = vbNo Then With Application .EnableEvents = False .Undo .Goto Target .EnableEvents = True Exit Sub End With Else Application.DisplayAlerts = False wkSht.Delete Application.DisplayAlerts = True End If End If Worksheets("Template").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = sName End Sub In article , "Simon" wrote: Hi guys, any help here greatly appreciated. I have an excel template file called Notes.xlt It has 2 worksheets in it called 'TOC' and 'TEMPLATE' In 'TOC', I will only be using column A, starting at Cell A1 and working down column A. What I would like to achieve is the following: Worksheet 'TOC' A 1 This is cell A1 2 cell A2 3 cell A3 Through some sort of macro, I would like to be able to enter in text into a cell in column A, then according to the row number eg.'2', copy the TEMPLATE worksheet to a new worksheet, and rename it to that row number, so the name of the new worksheet becomes '2'. If I accidentally try and create the new worksheet that exists with this name, to prompt me to give me the option of recreating it, or cancel. Is this possible in excel? Any help is really appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a copy of a worksheet
Hi JE,
Thank you for the code, I just dont know how to call your code from TOC. -- --------------------------------------------------------------------- "Are you still wasting your time with spam?... There is a solution!" Protected by GIANT Company's Spam Inspector The most powerful anti-spam software available. http://mail.spaminspector.com "J.E. McGimpsey" wrote in message ... One way: Put this in the worksheet code module of sheet TOC: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim wkSht As Worksheet Dim result As Integer Dim sName As String With Target If .Count 1 Then Exit Sub sName = .Row End With On Error Resume Next Set wkSht = Worksheets(sName) On Error GoTo 0 If Not wkSht Is Nothing Then result = MsgBox( _ Prompt:="Delete current sheet " & sName & "?", _ Buttons:=vbYesNo) If result = vbNo Then With Application .EnableEvents = False .Undo .Goto Target .EnableEvents = True Exit Sub End With Else Application.DisplayAlerts = False wkSht.Delete Application.DisplayAlerts = True End If End If Worksheets("Template").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = sName End Sub In article , "Simon" wrote: Hi guys, any help here greatly appreciated. I have an excel template file called Notes.xlt It has 2 worksheets in it called 'TOC' and 'TEMPLATE' In 'TOC', I will only be using column A, starting at Cell A1 and working down column A. What I would like to achieve is the following: Worksheet 'TOC' A 1 This is cell A1 2 cell A2 3 cell A3 Through some sort of macro, I would like to be able to enter in text into a cell in column A, then according to the row number eg.'2', copy the TEMPLATE worksheet to a new worksheet, and rename it to that row number, so the name of the new worksheet becomes '2'. If I accidentally try and create the new worksheet that exists with this name, to prompt me to give me the option of recreating it, or cancel. Is this possible in excel? Any help is really appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a copy of a worksheet
Sorry again. Last time I will post without testing JE
-- --------------------------------------------------------------------- "Are you still wasting your time with spam?... There is a solution!" Protected by GIANT Company's Spam Inspector The most powerful anti-spam software available. http://mail.spaminspector.com "M Hill" wrote in message ... Hi JE, Thank you for the code, I just dont know how to call your code from TOC. -- --------------------------------------------------------------------- "Are you still wasting your time with spam?... There is a solution!" Protected by GIANT Company's Spam Inspector The most powerful anti-spam software available. http://mail.spaminspector.com "J.E. McGimpsey" wrote in message ... One way: Put this in the worksheet code module of sheet TOC: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim wkSht As Worksheet Dim result As Integer Dim sName As String With Target If .Count 1 Then Exit Sub sName = .Row End With On Error Resume Next Set wkSht = Worksheets(sName) On Error GoTo 0 If Not wkSht Is Nothing Then result = MsgBox( _ Prompt:="Delete current sheet " & sName & "?", _ Buttons:=vbYesNo) If result = vbNo Then With Application .EnableEvents = False .Undo .Goto Target .EnableEvents = True Exit Sub End With Else Application.DisplayAlerts = False wkSht.Delete Application.DisplayAlerts = True End If End If Worksheets("Template").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = sName End Sub In article , "Simon" wrote: Hi guys, any help here greatly appreciated. I have an excel template file called Notes.xlt It has 2 worksheets in it called 'TOC' and 'TEMPLATE' In 'TOC', I will only be using column A, starting at Cell A1 and working down column A. What I would like to achieve is the following: Worksheet 'TOC' A 1 This is cell A1 2 cell A2 3 cell A3 Through some sort of macro, I would like to be able to enter in text into a cell in column A, then according to the row number eg.'2', copy the TEMPLATE worksheet to a new worksheet, and rename it to that row number, so the name of the new worksheet becomes '2'. If I accidentally try and create the new worksheet that exists with this name, to prompt me to give me the option of recreating it, or cancel. Is this possible in excel? Any help is really appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a copy of a worksheet Last 2 questions
JE, thank you.
It does what I want thank you. Just 2 more things Id like with this project... To recap: 2 worksheets: 'TOC' and 'TEMPLATE' So far your code: Creates a new worksheet when I enter in text on TOC based on the template worksheet and assigns it the new name of the row number I am in from the TOC worksheet (fantastic!). When I enter in text into TOC worksheet, lets say in cell A3 (or any row from TOC): I would like the text I type into this cell to be entered into cell A1 (always cell A1 no matter what row I am in from TOC) of the newly created worksheet. I would then like cell I am currently in (eg.A3) of the TOC worksheet to be hyperlinked to the newly created worksheet. Your great code.... Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim wkSht As Worksheet Dim result As Integer Dim sName As String With Target If .Count 1 Then Exit Sub sName = .Row End With On Error Resume Next Set wkSht = Worksheets(sName) On Error GoTo 0 If Not wkSht Is Nothing Then result = MsgBox( _ Prompt:="Delete current sheet " & sName & "?", _ Buttons:=vbYesNo) If result = vbNo Then With Application .EnableEvents = False .Undo .Goto Target .EnableEvents = True Exit Sub End With Else Application.DisplayAlerts = False wkSht.Delete Application.DisplayAlerts = True End If End If Worksheets("Template").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = sName End Sub -- --------------------------------------------------------------------- "Are you still wasting your time with spam?... There is a solution!" Protected by GIANT Company's Spam Inspector The most powerful anti-spam software available. http://mail.spaminspector.com "Simon" wrote in message ... Hi guys, any help here greatly appreciated. I have an excel template file called Notes.xlt It has 2 worksheets in it called 'TOC' and 'TEMPLATE' In 'TOC', I will only be using column A, starting at Cell A1 and working down column A. What I would like to achieve is the following: Worksheet 'TOC' A 1 This is cell A1 2 cell A2 3 cell A3 Through some sort of macro, I would like to be able to enter in text into a cell in column A, then according to the row number eg.'2', copy the TEMPLATE worksheet to a new worksheet, and rename it to that row number, so the name of the new worksheet becomes '2'. If I accidentally try and create the new worksheet that exists with this name, to prompt me to give me the option of recreating it, or cancel. Is this possible in excel? Any help is really appreciated. -- --------------------------------------------------------------------- "Are you still wasting your time with spam?... There is a solution!" Protected by GIANT Company's Spam Inspector The most powerful anti-spam software available. http://mail.spaminspector.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy w/o Creating Link in Excel 2007 | Excel Discussion (Misc queries) | |||
Help creating a macro to copy and paste a certain selection | Excel Discussion (Misc queries) | |||
Help creating a macro to copy and paste a certain selection | Excel Discussion (Misc queries) | |||
copy formula when creating new sheet | Excel Discussion (Misc queries) | |||
Edit/Move or Copy Creating .xls on Tab | Excel Worksheet Functions |