VBA Macro Loop for simple formatting

Question

I have a few basic macros, but I'd like to add a looping function to each. I'm looking for the starting code to kick of a loop and the ending code to close a loop on the last active worksheet.

Here is the current code:

Sub MQA_Shrink()
'
'
'
For Each sht In ThisWorkbook.Worksheets
Range("B:B,D:D,H:I,K:O").Select
Range("K1").Activate
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 1
Columns("B:F").Select
Columns("B:F").EntireColumn.AutoFit
Range("A1:F1").Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Selection.Merge
Next sht

End Sub

I'd just like this basic formatting macro to cycle through all the tabs and end.

I've mashed up different examples I've seen on here, but nothing seems to cycle through properly. They were all specifically tailored to the requester's project. Any advice?

score 0 · Answer 1 · edited May 23 '17 at 11:45

You need to assign the correct parentage to all range object or it will only refer to the active sheet.

Sub MQA_Shrink()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
    sht.Range("B:B,D:D,H:I,K:O").Delete Shift:=xlToLeft
    sht.Columns("B:F").AutoFit
    With sht.Range("A1:F1")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .Merge
    End With
Next sht

End Sub

Also after using the macro recorder one should go through and remove all the .Select and .Activate it slows down the code. See HERE

VBA Macro Loop for simple formatting

1 Answers1