+ Reply to Thread
Results 1 to 6 of 6

How to move to next sheet if Name is not present within sheet name

  1. #1
    Forum Contributor
    Join Date
    11-27-2009
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    377

    How to move to next sheet if Name is not present within sheet name

    Hi All,

    I am looking for some help on this issue?
    I a spreadsheet "DBMileage" I have mileage by drivers, I want to add the mileage by each day for one week to a "Master" file. Each sheet name on the master file has the drivers name and is the same as the drivers name on the "DBMileage" sheet.
    So, at present the code I have will add mileage to each sheet, but only if the drivers name is present, if the code finds a drivers name that is not on the spreadsheet, I receive a subscript error that the drivers name is not on the master.
    How do I add this part into the code to say if the drivers name is not on the spreadsheet tab, move onto the to next sheet?
    Thanks

    Please Login or Register  to view this content.
    ==========
    Bigroo1958
    Austin, Texas
    ==========

  2. #2
    Forum Contributor
    Join Date
    11-27-2009
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    377

    Re: How to move to next sheet if Name is not present within sheet name

    I have attached the two demo files pretty much stripped down to only the relevant data needed.
    Thanks
    Attached Files Attached Files

  3. #3
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,823

    Re: How to move to next sheet if Name is not present within sheet name

    You can intercept the "Subscript out of range" error with On Error Resume Next and evaluating Err.Number.
    If that's not zero then something isn't OK. Or if Err.Number is 9 then it's the "Subscript out of range" error.
    In that case you make the code jump to the next name/sheet.
    Make sure Error Trapping is set to "Break on unhandled errors". See Tools -> Options -> General
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  4. #4
    Valued Forum Contributor
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    1,134

    Re: How to move to next sheet if Name is not present within sheet name

    How about
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    11-27-2009
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    377

    Re: How to move to next sheet if Name is not present within sheet name

    Thanks to you both for the prompt reply!!
    Tsjallie, I am learning VBA and this has helped me a lot, I will remember this!

    Fluff13, your code modifications has done the trick and works as requested.
    Thanks to you both!

  6. #6
    Valued Forum Contributor
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    1,134

    Re: How to move to next sheet if Name is not present within sheet name

    Glad we could help & thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1