Go Back   TechArena Community > Software > Operating Systems
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Using vba code create new workbook and sheet

Operating Systems


Reply
 
Thread Tools Search this Thread
  #1  
Old 09-07-2011
Member
 
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?

Reply With Quote
  #2  
Old 09-07-2011
Member
 
Join Date: May 2009
Posts: 523
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.
Reply With Quote
  #3  
Old 09-07-2011
Member
 
Join Date: Apr 2009
Posts: 484
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
Reply With Quote
  #4  
Old 09-07-2011
Member
 
Join Date: May 2009
Posts: 532
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
Reply With Quote
  #5  
Old 09-07-2011
Member
 
Join Date: Apr 2009
Posts: 567
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
Reply With Quote
  #6  
Old 09-07-2011
Member
 
Join Date: May 2009
Posts: 503
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"
Reply With Quote
Reply

  TechArena Community > Software > Operating Systems
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Using vba code create new workbook and sheet"
Thread Thread Starter Forum Replies Last Post
How to create macros in Excel that scan data from second sheet Chini mao Windows Software 1 07-01-2012 06:34 PM
How can I use VBA to copy data from the closed workbook to the open workbook on Microsoft Excel 2010? Borislav Windows Software 3 11-07-2011 10:37 AM
how to copy data from sheet 1 to sheet 2 using macro Messenger Windows Software 3 26-10-2010 06:28 AM
How to create a contact sheet Bao Tips & Tweaks 2 10-07-2010 12:39 AM
How to create Good style sheet JEROLD12 Software Development 5 25-12-2009 11:51 PM


All times are GMT +5.5. The time now is 06:51 AM.