Run VBA across multiple spreadsheets with one button

Question

New to Excel so excuse me if I explain something wrong, happy to clear up any Q's in the comments, my VBA is most likely awful but I was only asked to do this yesterday for the first time.

I'm trying to clear carriage returns across all worksheets using a single button. I've found a way to clear them in the current worksheet and this runs fine:

Sub RemoveCarriageReturns()
Dim MyRange As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each MyRange In ActiveSheet.UsedRange
    If 0 < InStr(MyRange, Chr(10)) Then
        MyRange = Replace(MyRange, Chr(10), "")
    End If
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

I've searched for several ways to get this to run across all Worksheets, however none seem to work, my current code is:

Sub RemoveCarriageReturns()
Dim MyRange As Range
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

 For Each ws In Worksheets
  For Each MyRange In ActiveSheet.UsedRange
   Select Case UCase(ws.Name)
             Case "OTCUEXTR", "OTFBCUDS", "OTFBCUEL"
              With ws
               If 0 < InStr(MyRange, Chr(10)) Then
                MyRange = Replace(MyRange, Chr(10), "")
               End If
              End With
   End Select
  Next
 Next ws

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

I can see that its running through all sheets, but nothing changes beyond the first sheet (which works fine and removes the carriage returns.) Any advice on what I'm doing wrong here?

Apologies if this has been answered before, I've been unable to find a solution so far online.

Thanks for your help!

Why not use find and replace in the workbook rather than worksheet? — Nathan_Sav
– Nathan_Sav, Commented Nov 9, 2017 at 10:30
You are on the right path. Only need to replace ActiveSheet with ws should work. — newacc2240
– newacc2240, Commented Nov 9, 2017 at 10:31
@newacc2240 that worked, thank you for the quick response! Such a quick fix, but I would have taken forever to find that haha. — TurboTemple
– TurboTemple, Commented Nov 9, 2017 at 10:39

Wimpel · Accepted Answer · 2017-11-09 12:42:54Z

In my understanding, ActiveSheet is the sheet which displays on your screen. (Feel free to correct me if I'm wrong.) If you still want to use this method, you have to activate it first by ws.Activate
In your code, it will check the ws.name for every cell used. It may drag down the performance. A better way is check the name first, then do your stuff. In your case, since you only need to look for 3 worksheets and you already know their names, maybe setting an array first is a proper way.

...

Sub RemoveCarriageReturns()
    Dim MyRange As Range
    Dim NameList() As Variant
    NameList = Array("OTCUEXTR", "OTFBCUDS", "OTFBCUEL")
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    For i = 0 To 2
        With Worksheets(NameList(i))
            For Each MyRange In .UsedRange
                If 0 < InStr(MyRange, Chr(10)) Then
                    MyRange = Replace(MyRange, Chr(10), "")
                End If
            Next MyRange
        End With
    Next i

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Collectives™ on Stack Overflow

Run VBA across multiple spreadsheets with one button

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related