CODEWITHSUNDEEP
vbaexcelspreadsheet
Mehper C. Palavuzlar
Mehper C. Palavuzlar

Reputation: 10389

How to run the same code on all the worksheets in an Excel file

I want to execute the following VBA code for each worksheet at once in an Excel file:

Sub sample_code()    
    Columns("B:B").Select
    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A:A,D:J").Select
    Range("D1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("F:P").Select
    Selection.Delete Shift:=xlToLeft
    Columns("A:E").EntireColumn.AutoFit
    Columns("B:B").ColumnWidth = 30.86
    Range("A1:E1").Select
    Selection.Font.Bold = True
End Sub

How can I do that?

Upvotes: 0

Views: 5451

Answers (2)

dropper
dropper

Reputation: 73

Also, add Application.ScreenUpdating = False to the beginning and Application.ScreenUpdating = True at the end to make this go much faster.

Upvotes: 1

SWa
SWa

Reputation: 4363

Like this:

Sub sample_code()
    Dim ws As Worksheet

    For Each ws In Worksheets
        With ws
            .Columns("B:B").Replace What:=" ", Replacement:="", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
            .Range("A:A,D:J").Delete Shift:=xlToLeft
            .Columns("F:P").Delete Shift:=xlToLeft
            .Columns("A:E").EntireColumn.AutoFit
            .Columns("B:B").ColumnWidth = 30.86
            .Range("A1:E1").Font.Bold = True
        End With
    Next ws
End Sub

Upvotes: 5

Related Questions