Macro issue

[Detectiveclem]

Can anyone explain why I get a Run-time error '91' when trying to use the following code.

I want my Macro is simply remove unwanted text in a cell and leave whatever text is left. Below you will see that I wish the 'LAST NAME' to be removed but leave the remaining text.

Any help would be greatly appreciated.

Sub Macro22()
'
' Macro22 Macro
'
' Keyboard Shortcut: Ctrl+t
'
Range("D2").Select
ActiveCell.Replace What:="LAST NAME ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:="LAST NAME ", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
Range("H2").Select
ActiveCell.Replace What:="NAME OF SIPP", Replacement:="", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:="NAME OF SIPP", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
Range("I2").Select
ActiveCell.Replace What:="SIPP REF", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:="SIPP REF", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
End Sub

 

[jasonb75]

Assuming that the section in blue is the line causing the error, it means that you are trying to find text that does not exist in the sheet.

If you want to replace the text strings anywhere in the sheet then you would be better off using

With ActiveSheet
    .Cells.Replace What:="LAST NAME ", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
    ReplaceFormat:=False
End With

and removing the 'Find' sections altogether.

 

[Detectiveclem]

Thank you Jason, do I simply delete the text I have highlighted in blue and replace it with your text and then leave everything else as it was?

 

[jasonb75]

In theory the whole thing should be something like this, I've removed the space following "LAST NAME" as that could potentially cause errors but you can put it back in if you're sure it is needed.

Sub Detectiveclem()
With ActiveSheet
    .Cells.Replace What:="LAST NAME", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
    ReplaceFormat:=False
    .Cells.Replace What:="NAME OF SIPP", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
    ReplaceFormat:=False
    .Cells.Replace What:="SIPP REF", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
    ReplaceFormat:=False
End With
End Sub

 

[Detectiveclem]

Thank you so much, I won’t get a chance to try this until tomorrow, but will let you know how I get on.

A further question if you don’t mind. Is it possible to have a master spreadsheet which captures data using a macro from whatever other spreadsheet I have currently open.

I have hundreds of spreadsheets (all with different names) which I need to extract data from. I only need data from certain cells on each workbook and all the data I need is on the same cells references on each spreadsheet. I then need this data to be available on one master spreadsheet.

But any Macro I write only works on the named target spreadsheet. Is there a way to simply have the macro run on which ever target spreadsheet I have open at the time?

Sorry I am a complete novice when it comes to Macros as you can see.

Any advice or help would be gratefully received.
Thank you.

 

[jasonb75]

That should be possible but as it is a completely different question to the original one that you asked here you would need to start a new thread for it.