Thanks Thanks:  0
Likes Likes:  0
Page 3 of 3 FirstFirst 123
Results 21 to 30 of 30

Thread: VBA to generate new sheet

  1. #21
    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

    A11
    I get
    Method 'CountIf' of object 'WorksheetFunction' failed

  2. #22
    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

    OK. I did not test down to that part, I was just testing the building of your ranges.
    I don't think you can use non-contiguous ranges in the COUNTIF function. So I think you will need to have a block work through each range individually.
    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. #23
    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

    I think Im getting somewhere, but am struggling with the countIf part. I feel that it needs 'If Appli....countif(rng1, target) = 1' And ...Rng2 =0 ...And ..Rng3 =0 etc but everything Ive tried doesnt work.

    Any pointers???




    Private Sub Worksheet_Change(ByVal Target As Range)




    Application.ScreenUpdating = False




    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim Rng3 As Range
    Dim Rng4 As Range
    Dim isect1 As Range
    Dim isect2 As Range
    Dim isect3 As Range
    Dim isect4 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 Rng1 = Range("A11:G22")
    Set Rng2 = Range("A26:G34")
    Set Rng3 = Range("A38:G46")
    Set Rng4 = Range("A50:G58")
    Set isect1 = Intersect(Target, Rng1)
    Set isect2 = Intersect(Target, Rng2)
    Set isect3 = Intersect(Target, Rng3)
    Set isect4 = Intersect(Target, Rng4)


    If isect1 Is Nothing And isect2 Is Nothing And isect3 Is Nothing And isect4 Is Nothing Then Exit Sub




    If Application.WorksheetFunction.CountIf(Rng1, 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




    End Sub

  4. #24
    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

    You didn't quite finish the job. You only did the following block for the first range:
    Code:
    If Application.WorksheetFunction.CountIf(Rng1, 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
    What about the other three ranges? I believe you need to repeat this block for those three as well.
    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. #25
    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

    Yep, I have tried having 4 blocks for the 4 ranges, Unfortunately I get a 'subscript out of range' error. But also would it not generate a new sheet when a name is entered in Rng2 even if it already existed in Rng1, for example.

    Is the logic not ..

    if it is in Rng1 once, but not in Rng234, or if its in Rng2 once but not in Rng134, ....etc

  6. #26
    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

    Or is there a way to have

    'Something1' = Countif(Rng1, Target)
    'Something2' = Countif(Rng2, target)
    Etc

    And then a way of asking if something 1,2,3,4 = 1

    Or is this wishful foolishness.

    Thanks

  7. #27
    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 twl2009 View Post
    Or is there a way to have

    'Something1' = Countif(Rng1, Target)
    'Something2' = Countif(Rng2, target)
    Etc

    And then a way of asking if something 1,2,3,4 = 1

    Or is this wishful foolishness.

    Thanks
    I think you could probably use something like
    Code:
    If Application.CountIf(ActiveSheet.UsedRange, Target.Value) = 1 Then
    The UsedRange syntax would incorporate all of your separate ranges.
    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. #28
    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, that gives me

    'Subscript out of range'

  9. #29
    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 twl2009 View Post
    Thanks, that gives me

    'Subscript out of range'
    It would be nice to see how you used it. The subscript out of range message indicates that VBA cannot find the Object and it should be able to find the ActiveSheet and UsedRange without a problem. That leaves the Target variable. So if there is not Target variable initialized then I assume the statement is not used in the Worksheet_Change macro.
    Last edited by JLGWhiz; Apr 22nd, 2017 at 01:26 PM.
    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

  10. #30
    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 JLGWhiz. I know spot that the sheet name had been slightly altered at some point. It works across all the ranges now, except that if the same text is put in anywhere twice it gives a 'Object required' error message.

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