Macro from 2005 need help for 2010

[Jega]

Hi I have an old macro that needs to be converted to version 2012. Can anyone help me or is it going to be too complicated.
I copied it into a new macro but to no avail.
I would also like a smiley face button in excel to activate it.
Thanks in advance for any help.
Here it is:

Sub CleanUp()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:PersonName w:st="on">'</st1:PersonName><o></o>
<st1:PersonName w:st="on">'</st1:PersonName> CleanUp Macro<o></o>
<st1:PersonName w:st="on">'</st1:PersonName> Macro recorded 17/10/2005 by <st1:PersonName w:st="on">Charmaine Beckles</st1:PersonName><o></o>
<st1:PersonName w:st="on">'</st1:PersonName><o></o>
Selection.Find.ClearFormatting<o></o>
Selection.Find.Replacement.ClearFormatting<o></o>
With Selection.Find<o></o>
.Text = "&apos;"<o></o>
.Replacement.Text = "<st1:PersonName w:st="on">'</st1:PersonName>"<o></o>
.Forward = True<o></o>
.Wrap = wdFindAsk<o></o>
.Format = False<o></o>
.MatchCase = False<o></o>
.MatchWholeWord = False<o></o>
.MatchWildcards = False<o></o>
.MatchSoundsLike = False<o></o>
.MatchAllWordForms = False<o></o>
<o> </o>
End With<o></o>
<o> </o>
Selection.Find.Execute Replace:=wdReplaceAll<o></o>
Selection.Find.ClearFormatting<o></o>
Selection.Find.Replacement.ClearFormatting<o></o>
With Selection.Find<o></o>
.Text = "&"<o></o>
.Replacement.Text = "&"<o></o>
.Forward = True<o></o>
.Wrap = wdFindAsk<o></o>
.Format = False<o></o>
.MatchCase = False<o></o>
.MatchWholeWord = False<o></o>
.MatchWildcards = False<o></o>
.MatchSoundsLike = False<o></o>
.MatchAllWordForms = False<o></o>
<o> </o>
End With<o></o>
<o> </o>
Selection.Find.Execute Replace:=wdReplaceAll<o></o>
Selection.Find.ClearFormatting<o></o>
Selection.Find.Replacement.ClearFormatting<o></o>
With Selection.Find<o></o>
.Text = """<o></o>
.Replacement.Text = "<st1:PersonName w:st="on">'</st1:PersonName>"<o></o>
.Forward = True<o></o>
.Wrap = wdFindAsk<o></o>
.Format = False<o></o>
.MatchCase = False<o></o>
.MatchWholeWord = False<o></o>
.MatchWildcards = False<o></o>
.MatchSoundsLike = False<o></o>
.MatchAllWordForms = False<o></o>
<o> </o>
End With<o></o>
<o> </o>
Selection.Find.Execute Replace:=wdReplaceAll<o></o>
<o></o>
<o> </o>
End Sub<o></o>

 

[Andrew Poulsom]

Welcome to MrExcel.

That's a Word macro, not an Excel macro.

 

[Jega]

How do I get it to work in excel? They told me it once did.
Thanks

 

[Andrew Poulsom]

What other code is in the workbook? Does some other procedure call CleanUp? If not it will never have worked in Excel.

 

[Jega]

ADVERTISEMENT

No other code is in the workbook. It is a code to clean up all the &apos in the worksheets.

 

[Andrew Poulsom]

I don'tknow what you mean by &apos but to remove all ampersands try:

Sub RemoveAmpersands()
    Cells.Replace What:="&", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

 

[Jega]

ADVERTISEMENT

That may work. I copied that code into my personal.xlsb file. I changed a couple of things:

Sub CleanUP()
Cells.Replace What:="&apos", Replacement:="'", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

When I ran it, nothing appeared to happen.
I need it to run it once a week. Each week there will be sheets added. So, I would end up with many worksheets at the end of the month. I need this to catch all the &apos and replace them with ' and maybe a message at the end to say how many replacements it made so I know it is complete.

Thank you so much for your help
I have succeded in doing this with a Run Macro. but when I run the macro again, it doesn't seem to work.

 

[Andrew Poulsom]

Have you tried recording a macro while replacing the text manually? What VBA code does it give you?

 

[Jega]

Sub CleanUp()
'
' CleanUp Macro
'
'
Sheets(Array("20120401117", "20120401126", "20120401128", "20120402199", _
"20120404149", "20120409145", "20120411155", "20120412137", "20120412169", _
"20120413157", "20120415115", "20120415116", "20120415122", "20120416126", _
"20120416173", "20120416187", "20120416193", "20120417174", "20120418121", _
"20120418162", "20120418169", "20120418188", "20120418195", "20120419123", _
"20120419172")).Select
Sheets("20120401117").Activate
Sheets(Array("20120420112", "20120420129", "20120420175", "20120423146", _
"20120423175", "20120427156", "20120427168", "20120429133", "20120429135", _
"20120430174")).Select Replace:=False
Cells.Replace What:="&apos", Replacement:="'", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


It does not select all the worksheets. In fact nothing happens. I guess I will just have to do it manually

 

[Andrew Poulsom]

Try:

Sub CleanUp()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Cells.Replace What:="&apos", Replacement:="'", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Next ws
End Sub