For Loop Help

[chirayu]

Hi Guys,

The below "for loop" isn't working as I want it to, possibly because my code is incorrect. Unsure. Basically I want it to go through every sheet in the ActiveWorkbook that start with a month name & add a concatenate column (for later vlookup use), otherwise go to next sheet.

What its doing instead is running through the first time, then just keeps adding concatenate columns in the same sheet for the number of sheets there are in the file, instead of doing it on each sheet.

Dim ws as Worksheet
For Each ws In ActiveWorkbook.Worksheets
       If ws.Name Like "JAN*" _
       Or ws.Name Like "FEB*" _
       Or ws.Name Like "MAR*" _
       Or ws.Name Like "APR*" _
       Or ws.Name Like "MAY*" _
       Or ws.Name Like "JUN*" _
       Or ws.Name Like "JUL*" _
       Or ws.Name Like "AUG*" _
       Or ws.Name Like "SEP*" _
       Or ws.Name Like "OCT*" _
       Or ws.Name Like "NOV*" _
       Or ws.Name Like "DEC*" _
       Then
            Columns("A:A").Select
            Selection.Insert Shift:=xlToRight
            Range("A5").Select
            ActiveCell.Value = "CONC"
            ActiveCell.Offset(1, 0).Select
            If IsEmpty(ActiveCell.Offset(0, 1)) Then
                 ActiveCell.Formula = "=B6&J6"
                 Selection.Copy
                 Selection.PasteSpecial xlPasteValues
                 Application.CutCopyMode = False
            Else
                 ActiveCell.Formula = "=B6&J6"
                 Range("B50000").Select
                 Selection.End(xlUp).Select
                 Range("A6:A" & ActiveCell.Row).Select
                 Selection.FillDown
                 Selection.Copy
                 Selection.PasteSpecial xlPasteValues
                 Application.CutCopyMode = False
           End If
       End If
  Next ws

 

[Gary's Student]

You must Select ws:

Insert:

ws.Select

before:

Columns("A:A").Select

 

[Luke M]

Or, stop selecting things altogether, and acess the objects directly.

Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
    Select Case Left(ws.Name, 3)
   
    Case "JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"
        With ws
            .Columns("A:A").Insert Shift:=xlToRight
            .Range("A5").Value = "CONC"
            ActiveCell.Offset(1, 0).Select
            If IsEmpty(.Range("a6")) Then
                .Range("A6").Value = .Range("B6").Value & .Range("J6").Value
            Else
                With .Range("A6:A" & .Cells(.Rows.Count, "B").End(xlUp).Row)
                    .Formula = "=B6&J6"
                    .Copy
                    .PasteSpecial xlPasteValues
                End With
            End If
        End With
    End Select
Next ws
Application.ScreenUpdating = True

 

[chirayu]

Thanks @Gary's Student - guess I forgot to add ws.select
Also thanks @Luke M never tried a case argument before for worksheets, guess its something new to learn

 

[Luke M]

Yeah, I find it easier to use Case when I have a lot of possible Or type logic. Easier to write them all out that way.