Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 30

VBA to generate new sheet

This is a discussion on VBA to generate new sheet within the Excel Questions forums, part of the Question Forums category; Hi Joe I have added another sub and now this code isnt working at all, Im obviously doing something very ...

  1. #11
    Board Regular
    Join Date
    Jan 2016
    Posts
    176

    Default Re: VBA to generate new sheet

    Hi Joe

    I have added another sub and now this code isnt working at all, Im obviously doing something very stupid, but cant quite figure what.

    Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Address(False, False) = "B2" Then Call TitleCheck


    If Target.Address(False, False) = "F2" Then Call DateCheck


    End Sub






    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

  2. #12
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    38,663

    Default Re: VBA to generate new sheet

    You can only have one "Worksheet_Change" procedure per sheet!
    You would have to combine it into the one, something like this:
    Code:
    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
    
    If Target.Address(False, False) = "B2" Then Call TitleCheck
    
    If Target.Address(False, False) = "F2" Then Call DateCheck
    
    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
    By the way, if you use Code tags when posting your code, your code will look nicer (like mine!), as long won't lose all the formatting.
    Just paste the code, highlight it, and click on the Code tags button from the Editor menu (looks like a hash tag icon).
    Last edited by Joe4; Apr 21st, 2017 at 09:20 AM.
    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. #13
    Board Regular
    Join Date
    Jan 2016
    Posts
    176

    Default Re: VBA to generate new sheet

    I will use the code tags in future, thanks.

    That code gives me subscript out of range error. It generates the new sheet, but then fails before moving back to sheet 'labour week'

  4. #14
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    38,663

    Default Re: VBA to generate new sheet

    Try changing this line:
    Code:
    Application.Goto Worksheets("Weekly Labour").Range("A5")
    to
    Code:
    Sheets("Weekly Labour").Activate
    Range("A5").Select
    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. #15
    Board Regular
    Join Date
    Jan 2016
    Posts
    176

    Default Re: VBA to generate new sheet

    Thanks Joe, ive changed it now so it returns to next cell. Pretty pleased that I managed to do something for myself for once.
    However I was thinking about trying to get the sheets removed when the text is removed. The Stackoverflow suggestion of having a hidden sheet, so that when new text (actually names) is entered it adds it to hidden sheet ('staff'). Then if the names in the range A11:G23 dont match the names on the hidden sheet it deletes the sheet matching the missing name.
    does that make any sense and would it be possible.??

  6. #16
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    38,663

    Default Re: VBA to generate new sheet

    I think I would look at the two suggestions above that one. The one just above it looks promising.
    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. #17
    Board Regular
    Join Date
    Jan 2016
    Posts
    176

    Default Re: VBA to generate new sheet

    Ok cool. I will try and get my head around how to use one of the methods suggested. At the moment I'm struggling to get myrange to be multiple ranges.

    [CODE]Private Sub Worksheet_Change(ByVal Target As Range)




    Application.ScreenUpdating = False




    Dim r1, r2, r3, r4, myMultipleRange As Range
    Dim isect As Range




    If Target.Count > 1 Then Exit Sub




    If Target.Address(False, False) = "B2" Then Call TitleCheck




    If Target.Address(False, False) = "F2" Then Call DateCheck


    Set r1 = .Range("A11:G23")
    Set r2 = .Range("A26:G34")
    Set r3 = .Range("A38:G46")
    Set r4 = .Range("A50:G58")
    Set myMultipleRange = Application.Union(r1, r2, r3, r4)
    Set isect = Intersect(Target, myMultipleRange)
    If isect Is Nothing Then Exit Sub




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


    Sheets("Labour Week").Activate
    MyActiveCell.Select


    Application.ScreenUpdating = True




    EndSub/CODE]

  8. #18
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    38,663

    Default Re: VBA to generate new sheet

    One thing that stands out at first glance is the periods in front of your range. Remove those. You would only do that within a With statement, i.e.
    Code:
    Set r1 = Range("A11:G23")
    Set r2 = Range("A26:G34")
    Set r3 = Range("A38:G46")
    Set r4 = Range("A50:G58")
    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. #19
    Board Regular
    Join Date
    Jan 2016
    Posts
    176

    Default Re: VBA to generate new sheet

    Sorry Joe that was a mistake copy and pasting. I was just trying out the "."s to see if that would work. Unfortunately doesnt work without the "."s either.

  10. #20
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    38,663

    Default Re: VBA to generate new sheet

    It seemed to work fine for me.
    What cell did you try typing something in to?
    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!"

Page 2 of 3 FirstFirst 123 LastLast

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
  •  


DMCA.com