+ Reply to Thread
Results 1 to 14 of 14

Help with VBA to affect change on specific worksheets only

  1. #1
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Help with VBA to affect change on specific worksheets only

    Hello! I was hoping someone could assist me with editing the following code so that it would only work on the worksheets labeled the following: January, February, March, April, May, June, July, August, September, October, November, and December. The code worked beautifully before I needed to add more worksheets, which I believe are now affecting the code. It works on some sheets but not all. Due to the size of my workbook I'm not sure I can provide a sample but I will try if one is needed. Currently this is the code:

    Please Login or Register  to view this content.
    Any suggestions would be greatly appreciated!
    Attached Files Attached Files
    Last edited by lilsnoop; 02-26-2016 at 05:44 PM.

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Help with VBA to affect change on specific worksheets only

    Hi lilsnoop,

    Though I believe that separate tabs are usually not the way to go, try this:

    Please Login or Register  to view this content.
    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Help with VBA to affect change on specific worksheets only

    What is the problem that you are encountering? "Doesn't work on some sheets" is vague?
    What does it do/not do that you don't/do want it to? And under what conditions?
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Help with VBA to affect change on specific worksheets only

    Trebor76-Thank you for looking into this for me. I tried your code and I'm getting a Run-time error '13': Type mismatch debug window.

    When I hit debug it highlights the following in yellow:
    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Help with VBA to affect change on specific worksheets only

    Would have to have your workbook to help - MonthName is a User Defined Function (UDF) which may be causing the issue due to what's in (or not in) cell A2 of 'mySh'.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help with VBA to affect change on specific worksheets only

    Are you adding other than date?

    try change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Otherwise, need to see your workbook.

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help with VBA to affect change on specific worksheets only

    Quote Originally Posted by Trebor76 View Post
    Would have to have your workbook to help - MonthName is a User Defined Function (UDF) which may be causing the issue due to what's in (or not in) cell A2 of 'mySh'.
    MonthName is a vba built-in function. not UDF.

  8. #8
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Help with VBA to affect change on specific worksheets only

    Quote Originally Posted by mikerickson View Post
    What is the problem that you are encountering? "Doesn't work on some sheets" is vague?
    What does it do/not do that you don't/do want it to? And under what conditions?
    Hello mikerickson! The code is suppose to look at specific dates on a worksheet titled "Holiday" that are listed in Column A2 down (typically dates end at cell A12), and then based on those dates the code goes to the specific worksheets titled by month and then look for the exact date in Row 1 (starting at C1 over) and then put an "H" in row 2 directly under the exact date from row 1. I have some worksheets also titled "Jan", "Feb", and "1", "2", which I'm not sure if that confuses the code or not, but nevertheless the "H's" will work for some months but skip others.

    I appreciate any suggestions!

  9. #9
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Help with VBA to affect change on specific worksheets only

    The type mismatch suggests that the last cell in Holiday!A:A doesn't contain a number between 1 and 12. Perhaps there is a date or a space there.

  10. #10
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Help with VBA to affect change on specific worksheets only

    MonthName is a vba built-in function. not UDF.
    Ah, my bad Thanks jindon

    lilsnoop - MonthName needs a number (not a string that looks like a number) 1 to 12 representing a calendar month passed to it. If a string (or an error like #N/A) is passed to the function you will get a Run-time error '13': Type mismatch error.

  11. #11
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Help with VBA to affect change on specific worksheets only

    Thanks all for looking at this. I was trying to delete names and modify the workbook for test purposes; which is now attached. In doing that though it meant removing some data validations, names, a ton of formulas, other workbook connections, etc and now the old code started working again. So unfortunately I'm not sure by attaching this modified workbook you will experience the issues I'm having with the original.

  12. #12
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Help with VBA to affect change on specific worksheets only

    Hi lilsnoop,

    Is this what you're after?

    Please Login or Register  to view this content.
    Regards,

    Robert

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help with VBA to affect change on specific worksheets only

    lilsnoop,

    You have Error value in A5...
    try change to
    Please Login or Register  to view this content.

  14. #14
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Help with VBA to affect change on specific worksheets only

    jindon- I believe you found the problem! I'll test it out at work today and verify. Thanks everyone for all your suggestions!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need to lock formulas in a specific column so macro will not affect it
    By tx101 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-21-2016, 05:37 PM
  2. [SOLVED] Conditional Formatting / Macro to affect specific string within a cell
    By xandermacleod in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-16-2012, 02:06 PM
  3. [SOLVED] How do i make a change in one worksheet affect the others
    By elleblaze in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-21-2006, 06:10 PM
  4. use central formula on many worksheets, central affect allsheets
    By MidNiteRaver in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 AM

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