Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 30

Thread: VBA to generate new sheet

  1. #1
    Board Regular
    Join Date
    Jan 2016
    Posts
    226
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to generate new sheet

    Hi, I need a bit of code that when any text is entered into range A11:G23 if that specific text isnt matched in the range anywhere it creates a new sheet (a copy of sheet2) and names it with the text entered.

    Is this possible?

    Thanks

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    40,034
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to generate new sheet

    Right-click on the sheet tab name at the bottom of your sheet, select View Code, and paste this code in the resulting VBA Window.
    It should do what you want:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim myRange As Range
        Dim isect As Range
    
    '   If more than one cell updated at once, exit sub
        If Target.Count > 1 Then Exit Sub
        
    '   Check to see if update made in range A11:G23
        Set myRange = Range("A11:G23")
        Set isect = Intersect(Target, myRange)
        If isect Is Nothing Then Exit Sub
        
    '   Count to see how many cells in range match entry
        If Application.WorksheetFunction.CountIf(myRange, Target) = 1 Then
            Cells.Copy
            Sheets.Add After:=Sheets(Sheets.Count)
            ActiveSheet.Paste
            Application.CutCopyMode = False
            ActiveSheet.Name = Target.Value
        End If
        
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    9,499
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to generate new sheet

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim newSh As Worksheet
    If Target.Cells.Count > 1 Or Target = "" Then Exit Sub
    If Application.CountIf(Range("A11:G23"), Target.Value) = 1 Then
        Set newSh = Sheets.Add
        newSh.Name = Target.Value
    End If
    End Sub
    Copy code to worksheet code module of the sheet with source data.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    40,034
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to generate new sheet

    JLGWhiz,

    I see one potential problem with your code.
    I need a bit of code that when any text is entered into range A11:G23 if that specific text isnt matched in the range anywhere it creates a new sheet (a copy of sheet2) and names it with the text entered.
    They way that the code is written, they could enter something outside of range A11:G23, and if it matches exactly one item with range A11:A23, then it will create a new sheet.
    You code probably needs to be amended to make sure that the cell being updated (Target) resides in the range A11:G23 (which is why I used Intersect).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Jan 2016
    Posts
    226
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to generate new sheet

    Thanks Joe that works nicely, except it copies the sheet im on rather than sheet 2, which is 'Timesheet'
    Also it would be great to stay on the sheet im working on rather than go to the new sheet.

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    40,034
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to generate new sheet

    I thought you were on Sheet2 when running the code. Didn't realize that you were on a different sheet (not quite clear from the original question).
    Just need to add one simple line:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim myRange As Range
        Dim isect As Range
    
    '   If more than one cell updated at once, exit sub
        If Target.Count > 1 Then Exit Sub
        
    '   Check to see if update made in range A11:G23
        Set myRange = Range("A11:G23")
        Set isect = Intersect(Target, myRange)
        If isect Is Nothing Then Exit Sub
        
    '   Count to see how many cells in range match entry
        If Application.WorksheetFunction.CountIf(myRange, Target) = 1 Then
            Sheets("Sheet2").Activate
            Cells.Copy
            Sheets.Add After:=Sheets(Sheets.Count)
            ActiveSheet.Paste
            Application.CutCopyMode = False
            ActiveSheet.Name = Target.Value
        End If
        
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    9,499
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to generate new sheet

    Quote Originally Posted by Joe4 View Post
    JLGWhiz,

    I see one potential problem with your code.

    They way that the code is written, they could enter something outside of range A11:G23, and if it matches exactly one item with range A11:A23, then it will create a new sheet.
    You code probably needs to be amended to make sure that the cell being updated (Target) resides in the range A11:G23 (which is why I used Intersect).
    Yep, you're right, Joe4. Senility strikes again!
    regards, JLG
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  8. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    40,034
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to generate new sheet

    Yep, you're right, Joe4. Senility strikes again!
    We can't beat Mother Nature. She always wins in the end.
    I have had perfect vision all my life, and am now starting to have issues reading small things.
    I fear it is only the beginning!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    Board Regular
    Join Date
    Jan 2016
    Posts
    226
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to generate new sheet

    Thanks Joe. I ended up with this.

    But I realise now that I will need the new sheet to be deleted if the specific text is deleted and doesn't appear anywhere else in the range. I imagine this is not so easy.


    Private Sub Worksheet_Change(ByVal Target As Range)


    Application.ScreenUpdating = False


    Dim myRange As Range
    Dim isect As Range


    If Target.Count > 1 Then Exit Sub


    Set myRange = Range("A11:G23")
    Set isect = Intersect(Target, myRange)
    If isect Is Nothing Then Exit Sub

    If Application.WorksheetFunction.CountIf(myRange, Target) = 1 Then
    Sheets("Timesheet").Cells.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Name = Target.Value & " Timesheet"
    End If

    Application.Goto Worksheets("Weekly Labour").Range("A5")


    Application.ScreenUpdating = True

    End Sub

  10. #10
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    40,034
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to generate new sheet

    But I realise now that I will need the new sheet to be deleted if the specific text is deleted and doesn't appear anywhere else in the range. I imagine this is not so easy.
    Trying to capture text that is no longer there is a bit tricky (see: How do I get the old value of a changed cell in Excel VBA? - Stack Overflow). I prefer to do it another way.
    Don't let them enter anything to the sheet directly. Have a form or button that they can click if they want to add or delete something, and then you can use that VBA code to capture it, and do whatever needs to be done.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com