VBA Find and Replace in another workbook

[Suavis]

Hi All,

I have several user who use capture forms to record their daily productivity. The productivity data is recorded in a central workbook on a share drive named OPDB.xlsx. One of the fields automatically records the user name as per their log-in credentials. For some reason the system user name format is not consistent. In some cases it records the user's staff ID number (i.e. A123456), and in other cases their names (i.e. Smith, John J).

Another workbook named Productivity.xlsm, saved on the same share drive, contains pivot tables and graphs reflecting team and individual productivity stats. The Productivity Stats however use the User Name, which makes for very confusing stats.

In an attempt to standardise the User Name Field, I wrote the following Macro and saved it to my Personal Macros, which works perfectly when I run the code.

Sub Replace_User_Name()

Dim BackupDB as Workbook
Set BackupDB = Workbooks.Open("Z:\Forms\Capture Forms\OPDB.xlsx")
With BackupDB
Worksheets("Outcome Data").Activate

Cells.Replace What:="a096506", Replacement:="John Smith",  _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

.Save
.Close
End With

End Sub

If I however add a Command Button to Productivity.xlsm (using activeX controls), and I run the exact same code in Productivity.xlsm workbook, it does not work.

No errors or debug is produced. It opens OPDB.xlsx, runs the code, saves and closes, but the code does not actually change / replace the data.

If I run the identical code in my personal macros, it works every time.

Can anyone offer some advise or insight?

Thanks
Johan

 

[AlphaFrog]

Give this a try. This is a guess.

[color=darkblue]Sub[/color] Replace_User_Name()
    
    [color=darkblue]Dim[/color] BackupDB [color=darkblue]As[/color] Workbook
    [color=darkblue]Set[/color] BackupDB = Workbooks.Open("Z:\Forms\Capture Forms\OPDB.xlsx")
    [color=darkblue]With[/color] BackupDB
        [COLOR="#FF0000"].Worksheets("Outcome Data").[/COLOR]Cells.Replace What:="a096506", Replacement:="John Smith", _
                                                  LookAt:=xlPart, SearchOrder:=xlByRows, _
                                                  MatchCase:=False, SearchFormat:=[color=darkblue]False[/color], _
                                                  ReplaceFormat:=False
        .Save
        .Close
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Suavis]

Brilliant. Works perfectly.

Thanks a mil.
Johan