Results 1 to 6 of 6

Thread: Using vba code create new workbook and sheet

  1. #1
    Join Date
    Jun 2011
    Posts
    101

    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?

  2. #2
    Join Date
    May 2009
    Posts
    527

    Re: Using vba code create new workbook and sheet

    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.

  3. #3
    Join Date
    Apr 2009
    Posts
    488

    Re: Using vba code create new workbook and sheet

    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

  4. #4
    Join Date
    May 2009
    Posts
    539

    Re: Using vba code create new workbook and sheet

    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

  5. #5
    Join Date
    Apr 2009
    Posts
    569

    Re: Using vba code create new workbook and sheet

    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

  6. #6
    Join Date
    May 2009
    Posts
    511

    Re: Using vba code create new workbook and sheet

    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"

Similar Threads

  1. Replies: 1
    Last Post: 07-01-2012, 06:34 PM
  2. Replies: 3
    Last Post: 11-07-2011, 10:37 AM
  3. how to copy data from sheet 1 to sheet 2 using macro
    By Messenger in forum Windows Software
    Replies: 3
    Last Post: 26-10-2010, 06:28 AM
  4. How to create a contact sheet
    By Bao in forum Tips & Tweaks
    Replies: 2
    Last Post: 10-07-2010, 12:39 AM
  5. How to create Good style sheet
    By JEROLD12 in forum Software Development
    Replies: 5
    Last Post: 25-12-2009, 11:51 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,646,117.43178 seconds with 17 queries