How do I run my macro only on one specific sheet?

  • I have an excel workbook with about 20 different sheets, and I want to make an expiry date pop-up notification based on sheet 12 (Shelf Life Tracking) but it keeps saying Run-time error '13', probably because the code does not specify which sheet it should apply to. Below is the code! How do I specify that I want this macro to run on sheet12 (Shelf Life Tracking)?

  • Code
    Option Explicit
    Private Sub Workbook_Open()
    Dim DateDueCol As Range
    Dim DateDue As Range
    Dim NotificationMsg As String
    Set DateDueCol = Sheet1.Range("F7:F100") 'the range of cells that contain your due dates
    For Each DateDue In DateDueCol

    I tried to highlight the change in your code but it appears that did not work as desired.

    The sixth line down (begins with Set DateDueCol) .... insert the appropriate sheet name before the work Range.

  • This code (Range("F7:F100") will only work on the active sheet because you do not explicitly use a sheet.

    Probably you can improve the code by not looping as well. Attach an example of your WorkBook.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!