VBA loop of inserting and cutting columns?

Question

I am unsure how to create this loop so I did a recorded macro of it. How can I make this a loop that runs through all worksheets?

Sheets("AT").Select
    Columns("F:F").Select
    Selection.Cut
    Columns("E:E").Select
    Selection.Insert Shift:=x1ToRight
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("K:K").Select
    Selection.Cut
    Columns("J:J").Select
    Selection.Insert Shift:=x1ToRight

I think what you are looking for is: `For Each wsName In Sheets` and `Next wsName`, where `wsName` is dimensioned as Worksheet. — Rey Juna, Aug 03 '18 at 21:43
@BigBen thanks, I thought so too. Spotting things like that should be worth a automatic badge or something \... lol — ashleedawg, Aug 03 '18 at 21:47

score 1 · Accepted Answer · answered Aug 03 '18 at 22:20

You cannot .Select a cell/range on a worksheet that isn't the ActiveSheet so essentially you have two choices; either .Activate each worksheet in a loop before running your code on that worksheet (not recommended) or modify your code so that .Select and .Activate is no longer needed (recommended).

Option Explicit

Sub Macro1()

    Dim w As Long

    For w = 1 To Worksheets.Count
        With Worksheets(w)
            .Columns("F:F").Cut
            .Columns("E:E").Insert Shift:=xlToRight

            .Columns("H:I").Insert Shift:=xlToRight

            .Columns("K:K").Cut
            .Columns("J:J").Insert Shift:=xlToRight
        End With
    Next w

End Sub

VBA loop of inserting and cutting columns?

1 Answers1