Excel 2007 VBA code to run search/replace on multiple worksheets ?

[TomYC]

I have a file with numerous worksheets. Every time I add new, imported data to the file, I must run a search and replace on one or more columns in one or more worksheets, or else my data is not "cleaned." I would like to automate this in case I forget!
Surely there is an OnClose event in which all cells in worksheets could be selected, and a search and replace operation run that strips off some unnecessary text strings, after which the file is saved, or even as part of the save process.
I have experimented with macros, would prefer code idea...
Thanks--
T.Y.

 

[SkipVought]

Hi,

I already advised you of the Workbook events you might consider.

As far as code is concerned, use your macro recorder and then post your recorded code and questions for help customizing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

I don't know what conversations happend previously with Skip. I will say that Skip knows his stuff so its worth listening to what he has to say.

However from what I am picking up it sounds like you are frustrated about some part of the process or are not grasping how something works.

The simple answer is to start the macro recorder, do your find and replace (Control H) for all items you will need. Stop the recorder and then in the VBA editor go to the workbook - located about the modules. You should select workbook in the top left dropdown and BeforeClose in the top right dropdown, then copy the macro that was just recorded into the new location.


Otherwise if you are ok with coding you can look up replace in VBA help and follow that for an example. Again, put it in the workbook as a before close event.

 

[TomYC]

Thanks, Unsolved--
Skip directed me to the appropriate forum.
And I had already created a macro, that I was hoping to generalize or at least automate.
So I took your suggestion, and from my Macro NSUH(), I created, somewhat redundant, the following in ThisWorkbook:
Public Sub BeforeClose()
Sub NSUH()
'
' NSUH Macro
'
'
' Keyboard Shortcut: Ctrl+n
'
Sheets(Array("2011", "JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT" _
, "NOV", "DEC")).Select
Sheets("2011").Activate
Cells.Select
Selection.Replace What:="NSUH - ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
End Sub

And of course it hasn't worked (yet), so there must be something...

 

[SkipVought]

Public Sub BeforeClose()
    NSUH
End Sub


Sub NSUH()
'
' NSUH Macro
'
'
' Keyboard Shortcut: Ctrl+n
'
    Sheets(Array("2011", "JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT" _
        , "NOV", "DEC")).Select
    Sheets("2011").Activate
    Cells.Select
    Selection.Replace What:="NSUH - ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

Skip you beat me to it.

 

[TomYC]

This code is not firing. I copied and pasted in just as above; it is in the thisworkbook area. Saved the file, then opend the file and edited it, then closed. I also put in some MsgBox lines so I would see if any part of it runs.
Should I try another option such as BeforeSave or is it OnSave?

 

[SkipVought]

Sub NSUH()
'
' NSUH Macro
'
'
' Keyboard Shortcut: Ctrl+n
'
    Sheets(Array("Sheet2", "Sheet3")).Activate
    Cells.Select
    Selection.Replace What:="Skip", Replacement:="Other", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False[b]
    ThisWorkbook.Save[/b]
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

No go; I think I need to learn how to trigger any event at all in Excel!

 

[SkipVought]

try using the BeforeSave event

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

I need to find where Events is, and then I will try that one; unlike in Access, it is less intuitive in the VBA window!

 

[SkipVought]

in the ThisWorkbook Code Window, there are TWO DropDown boxes at the top. Chose WORKBOOK from the LEFT DD and see the EVENTS in the RIGHT DD.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

The events that Skip is refering to is where you selected BeforeClose from the dropdown box.

Instead of selecting BeforeClose select BeforeSave.

So your code would look something like this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

NSUH

End sub

 

[TomYC]

thank you both; that was embarassingly easy to find.
I still have Sub NSUH() coming up with an ambiguous name error message, but there's progress.

 

[SkipVought]

I would MOVE your NSUH to a MODULE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I would MOVE your NSUH to a MODULE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

Making progress!
I have it working on one sheet, but the Array is triggering a 'not support this object or method' error.

 

[SkipVought]

Do you have this string :="NSUH - " on sheets other than those in the array?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

Don't see any; I'm researching ambiguous name--

 

[UnsolvedCoding]

Usually ambiguous name means that something is duplicated or cross named. For example naming two subs the same thing, calling a module and a sub the same thing, two variables being called the same thing etc.