Private Sub not running

[gtd526]

Hello,
My Private Sub in not running when the workbook is opened. I have it stored in "ThisWorkbook".
Thank you.

Here is the code Im using:

Private Sub PastePotentialValue()
'paste special values in potential hours on Fridays date

    Dim foundRng As Range

    Sheets("BJs Income").Select
    Range("Q1").Value = "HELP"

    Set foundRng = Range("A:A").Find(CStr(Date), LookIn:=xlValues, lookat:=xlWhole)
  
    If Not (foundRng Is Nothing) Then
        foundRng.Select
        ActiveCell.Offset(0, 14).Select
        Selection.Copy
        ActiveCell.PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Else
    End If
        Application.CutCopyMode = alse

End Sub

 

[Joe4]

My Private Sub in not running when the workbook is opened. I have it stored in "ThisWorkbook".

If you want to run it automatically when opening the workbook, it not only has to be in the "ThisWorkbook" module, but it MUST be named:

Private Sub Workbook_Open()

so it runs as an event procedure.

This is non-negotiable. You cannot change the name.

If you do not want to rename it, then just call it from that, i.e.

Private Sub Workbook_Open()
    Call PastePotentialValue
End Sub

 

[gtd526]

If you want to run it automatically when opening the workbook, it not only has to be in the "ThisWorkbook" module, but it MUST be named:

Private Sub Workbook_Open()

so it runs as an event procedure.

This is non-negotiable. You cannot change the name.

If you do not want to rename it, then just call it from that, i.e.

Private Sub Workbook_Open()
    Call PastePotentialValue
End Sub

I renamed to Workbook_Open().
I didn't realize to use this Name for opening a workbook.
Thank you.

 

[Joe4]

I renamed to Workbook_Open().
I didn't realize to use this Name for opening a workbook.

Excel has a number of what are known as "Event Procedures". These are "events" are things that happen in Excel that can automatically kick-off VBA code to run.
However, Excel needs to be able to connect the VBA code with the Event calling it. So each event has its own precise name. Name it anything else, and it isn't looking for the event.

There are two main types of events - those associated with the Workbook (found in the "ThisWorkbook" module), and those associated with Sheets (found in the various "Sheet" modules).

See here for more details on this: Events In Excel VBA