CODEWITHSUNDEEP
vbaexcel
Rob
Rob

Reputation: 85

Running my Macro through all Worksheets in a Workbook

currently I have a macro that runs and finds headers "DATE" and "DT" in the Active Worksheet and reformats the cells in the column. I'm hoping to make it run through all Worksheets and run the macro. Here's the current VBA code:

Sub DateFormatting()
    Dim x As Variant
    With ActiveWorksheet
        For Each x In .Range(.Cells(2, 1), .Cells(2, .Columns.Count).End(xlToLeft)).Cells
            If InStr(1, Replace(x.Value, "DATE", "DT", , , vbTextCompare), "DT", vbTextCompare) Then
                Intersect(.UsedRange, .UsedRange.Offset(2), x.EntireColumn).NumberFormat = "m/d/yyyy h:mm"
            End If
        Next
    End With
End Sub

Upvotes: 1

Views: 306

Answers (1)

Ralph
Ralph

Reputation: 9434

Just to summarize all comments:

Option Explicit

Sub DateFormatting2()

Dim x As Variant
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    With ws
        For Each x In .Range(.Cells(2, 1), .Cells(2, .Columns.Count).End(xlToLeft)).Cells
            If InStr(1, Replace(x.Value2, "DATE", "DT", , , vbTextCompare), "DT", vbTextCompare) Then
                Intersect(.UsedRange, .UsedRange.Offset(2), x.EntireColumn).NumberFormat = "m/d/yyyy h:mm"
            End If
        Next x
    End With
Next ws

End Sub

Comments:

  1. You missed the s at the end of For Each ws In ThisWorkbook.Worksheet
  2. You had the for each ws loop inside the with ws statement while it should be the other way around. You cannot use ws if it isn't initiated yet.
  3. To end a With you need to End With and not Next
  4. Option Explicit just for good measures.

Upvotes: 1

Related Questions