VBA Loop

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,970
Office Version
  1. 2010
Platform
  1. Windows
How could I loop this, to work on each sheet in my workbook

Currently sheets exist Jan - August

Thanks

Code:
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Have you tried just doing?
For i = 1 to sheets.count
sheets(i).select
' your stuff
next
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Guys, I dont follow you

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

Many thanks

Mark
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,211,686
Messages
6,103,296
Members
447,853
Latest member
olddutch7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top