Using vba code create new workbook and sheet

Please someone help me. My macro creates a new workbook with sever worksheets in it. Every new worksheet get formatted with color following with words and math formulas in it. I failed to add VBA to a particular sheet or sheet object. Below is the code which I am trying:

Code:

---

Private Sub Worksheet_SelectionChange(ByVal target As Range)
If target.Address = "$D$1" Then caly2.Show
End Sub

---

Unnecessary cells get generated how to dele them? Also how to create new tab and worksheet with this code already in it in Excel 2007?

I have the simplest method for you. Only thing you need to do is have a worksheet template set up. For example lets take Sheet2 and say its empty with Worksheet_SelectionChange code that you have posted in your question. Then use the Worksheet.Copy method to create a fresh sheet:

Code:

---

Sub foo()

    Sheet1.Copy after:=Worksheets(Worksheets.Count)
   
End Sub

---

Keep in mind that the created sheet will have event handler code in it.

There is another way, to create a new fresh sheet add Worksheet_SelectionChange (ByVal Target As Range). Add it in the new worksheet named test. You can take reference from below:

Code:

---

If Target.Address = "$D$1" Then caly2.Show


Sub AddCodeWithCode()
Dim strProcLine As String

//This procedure adds a new worksheet to the active workbook.
Sheets.Add.Name = "Test"

With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule

    'Add the empty procedure
    .CreateEventProc "SelectionChange", "Worksheet"

End With
End Sub

---

You can also try another option such as to create new sheet. You can create new sheets using Worksheets.Add. Rest things will be handled by doing programming in the class module.

Code:

---

Sub AddCodeWithCode()

'adds in new sheet
Sheets.Add.Name = "Test3"

This procedure sets up the workbook procedure
With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).codemodule
    'Add the empty procedure
            .InsertLines 1, "Private Sub Worksheet_SelectionChange(ByVal target As Range)"
            .InsertLines 2, "If Target.Address = ""$D$1"" Then caly2.Show"
            .InsertLines 3, "end sub"

End With
End Sub

---

I can help you with the code inserted and works. But if you use a single syntax then it wont make any difference. You have to use two syntaxes simultaneously. You can refer the below two codes and just copy paste it in your actual source.

Code:

---

ActiveWorkbook.Save
 ActiveWorkbook.Close

Sub foo()

    Dim wkbNew As Workbook
   
    Set wkbNew = Workbooks.Add(template:="C:\My Templates\My Workbook With Code In.xls")
   
    'etc...
   
End Sub

---

Use the below code it will sets up the workbook procedure. You don’t have to set the insertlines simultaneously. I hope this code works for you and your query gets solved forever.

Code:

---

Private Sub Worksheet_SelectionChange(ByVal target As Range)
If target.Address = "$D$1" Then caly2.Show
End Sub

---

Code:

---

//This procedure sets up the workbook procedure
With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).codemodule
    'Add the empty procedure
            .InsertLines 1, "Private Sub Worksheet_SelectionChange(ByVal target As Range)"
            .InsertLines 2, "If Target.Address = ""$D$1"" Then caly2.Show"
            .InsertLines 3, "end sub"

---