VBA Loop

[MarkAndrews]

How could I loop this, to work on each sheet in my workbook

Currently sheets exist Jan - August

Thanks

Application.ScreenUpdating = False
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(11, 1))
    Columns("F:F").Select
    Selection.TextToColumns Destination:=Range("F1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(11, 1))
    Columns("D:D").Select
    Selection.NumberFormat = "dd/mm/yy"
    Columns("F:F").Select
    Selection.NumberFormat = "dd/mm/yy"
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Authact Received"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Client"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Networkdays"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Introducer"
    Cells.Select
    Cells.EntireColumn.AutoFit
    Rows("1:1").Select
    Selection.Font.Bold = False
    Selection.Font.Bold = True
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]=""null"","""",IF(ISNUMBER(RC[-1]),""Yes""))"
    Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, -1).End(xlDown).Offset(0, 1))
    Range("G2:G1801").Select
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],Workings!RC[-7]:R[21]C[-6],2,0)"
    Range("H2").Select
    Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, -1).End(xlDown).Offset(0, 1))
    Range("H2:H1801").Select
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],Workings!R2C1:R23C2,2,0)"
    Range("H2").Select
    Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, -1).End(xlDown).Offset(0, 1))
    Range("H2:H1801").Select
    Columns("H:H").EntireColumn.AutoFit
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-4]=""null"","""",NETWORKDAYS(RC[-5],RC[-4]))"
    Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, -1).End(xlDown).Offset(0, 1))
    Range("I2:I1801").Select
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],Workings!R2C4:R56C5,2,0)"
    Columns("J:J").EntireColumn.AutoFit
    Selection.AutoFill Destination:=Range("J2:J1801")
    Range("J2:J1801").Select
    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Cells.EntireColumn.AutoFit
    Range("H7").Select
    Range("a1").Select
    Application.ScreenUpdating = True
End Sub

 

[jdavis9]

Have you tried just doing?
For i = 1 to sheets.count
sheets(i).select
' your stuff
next

 

[Airfix9]

2 questions:

Are they the ONLY sheets in the book?

Is the list of sheets fixed? i.e. will you be adding more sheets as time progresses?

 

[MarkAndrews]

2 questions:

Are they the ONLY sheets in the book?

Is the list of sheets fixed? i.e. will you be adding more sheets as time progresses?

I'll eventually be adding Sep, Oct, Nov & Dec

Other than than, yes these sheets are fixed

Apologies, I've never used looping before

 

[jdavis9]

Humm, instead of "sheets", use "worksheets" in case you have charts...

 

[SteveO59L]

Dim ws as Worksheet
For each ws in Worksheets

<<< Your code here >>

Next ws

 

[Airfix9]

Pop off to get a sandwich and find that the two suggestions that I was going to make have been posted!

Either is just as quick.

 

[jdavis9]

lol, you snooze, you lose.....

 

[MarkAndrews]

Guys, I dont follow you

Is it possible for someone to physically copy my code & amend

Many thanks

Mark

 

[Airfix9]

For i = 1 to Worksheets.count
Worksheets(i).select
' your stuff
next

Seems fairly simple to me - put your code in the middle of that (where it says "your stuff"

The opening instruction tells it to count all the sheets and select the first. The "next" tells it to loop. It will finish when it reaches the end.