CODEWITHSUNDEEP
vbaexcel
DerekP
DerekP

Reputation: 69

Run Macro for any Active or Open Workbook?

So I've created a simple Macro to:

I have this macro working, but can only get it to work when I embed the macro code into a worksheet.

Here is the code;

Sub UpdateChartParams()

Dim Chart_Parameters As Worksheet

Sheets("Chart_Parameters").Visible = True

Sheets("Chart_Parameters").Select
Cells.Replace What:="testtext", Replacement:="newtext", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Sheets("Chart_Parameters").Visible = False

End Sub

What I need to do is run this file against any open workbooks if possible.

Upvotes: 1

Views: 4417

Answers (2)

Robert Ilbrink
Robert Ilbrink

Reputation: 7973

Derek,

One way to do this is to add your macro's to the file PERSONAL.XLSB. This file will be loaded in the background every time you start Excel. Initially the PERSONAL.XLSB file will NOT be there.

To automatically create this file, just start recording a "dummy" macro (with the record button on the left-bottom of a spreadsheet) and select "Personal Macro Workbook" to store it in. After recording your macro, you can open the VBA editor with [Alt]+[F11] and you will see the PERSONAL.XLSB file with the "dummy" macro.

I use this file to store loads of general macro's which are always available. I have added these macro's to my own menu ribbon.

One disadvantage of this common macro file is that if you launch more than one instance of Excel, you will get an error message that the PERSONAL.XLSB file is already in use by Excel instance Nr. 1. This is no problem as long as you do not add new macro's at this moment.

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166790

This will update any open workbook(s) with a matching sheet. No need to unhide to run the replace.

Sub UpdateChartParams()

    Dim Chart_Parameters As Worksheet
    Dim wb As Excel.Workbook

    For Each wb In Application.Workbooks
    If wb.Name <> ThisWorkbook.Name Then

        Set Chart_Parameters = Nothing
        On Error Resume Next 'ignore error if no such sheet...
        Set Chart_Parameters = wb.Worksheets("Chart_Parameters")
        On Error GoTo 0

        If Not Chart_Parameters Is Nothing Then

            Chart_Parameters.Cells.Replace What:="testtext", _
                   Replacement:="newtext", LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False

        End If

    End If
    Next wb

End Sub

Upvotes: 2

Related Questions