none
How to Apply a Macro to All Sheets in a Workbook including newly created sheets RRS feed

  • Question

  • Hi All

    I'm trying to get the below code to run on every sheet in a workbook including any new sheets that will be created.

    Option Explicit

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

        If Not Intersect(Range("H2"), Target) Is Nothing Then
       
        purchasemsg 'name of macro
           
        End If
        Next Sheet
       
    End Sub

    I've tried applying it to the ThisWorkbook section with no success. I can provide the full code of the macro this code is trying to run; purchasemsg; if necessary.

    I've tried the loop through all worksheets method but that runs the macro on the same sheet multiplied by the number of sheets immediately before the cell in question H1 has been filled and selection changed. Loop example that have tried and doesn't work as below:

    Dim wb As Workbook
    Dim sht As Worksheet

    For Each wb In Application.Workbooks

    If wb.Name <> "PERSONAL.xlsb" Then
        For Each sht In wb.Worksheets
        
          'Add today's date to cell A1 of worksheet
            sht.Range("A1") = Date
        
        Next sht
      End If
    Next wb

    End Sub

    Thanks


    Red Hood - Y.O.L.T

    Friday, July 3, 2015 2:25 PM

All replies

  • Use the Workbook_SheetSelectionChange event handler instead, and place it in the code module for ThisWorkbook...

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
        If Not Intersect(Range("H2"), Target) Is Nothing Then
       
            purchasemsg 'name of macro
        
        End If
       
    End Sub

    Hope this helps!

    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Friday, July 3, 2015 3:07 PM
  • Hi Jason,

    Where/when is your macro(the above) be executed?
    I suppose it is better for the macro to be executed/described in "ThisWorkbook"(Excel Object), in sub "Workbook_Open".

    Private Sub workbook_open()
        Dim sht As Worksheet
        For Each sht In Worksheets
            'Add today's date to cell A1 of worksheet
            sht.Range("A1") = Date
        Next sht
    End Sub

    If you want to handle more than one Excel file(many workbooks) in some folder, the above code should be modified a little and placed in anoher place, and it is not sub "workbook_open".

    Regards,


    • Edited by Ashidacchi Saturday, July 4, 2015 6:59 AM
    Saturday, July 4, 2015 6:46 AM