Excel VBA Loop?

Question

I am new to creating VBA macros. I recorded the macro below, but I need this done for 18 other worksheets in the workbook. I do not know how to create a loop. Can it be done from this recorded macro? Sorry for the long code. Again this code is used on 18 other worksheets and am unsure how to create a loop.

Sheets("C3 CONW INW OPIS_CMA").Select
Range("G1").Select
ActiveCell.FormulaR1C1 = "PSTRIK"
Range("A1").Select
ActiveCell.FormulaR1C1 = "PRECID"
Range("A2").Select
ActiveCell.FormulaR1C1 = "P"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A26"), Type:=xlFillDefault
Range("A2:A26").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "PEXCH"
Range("C2").Select
ActiveCell.FormulaR1C1 = "7"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C26"), Type:=xlFillDefault
Range("C2:C26").Select
Columns("O:O").Select
Selection.Delete Shift:=xlToLeft
Columns("N:N").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Cut
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Selection.Cut
Columns("K:K").Select
Selection.Insert Shift:=xlToRight
Range("I1").Select
ActiveCell.FormulaR1C1 = "PQTY"
Range("G1").Select
ActiveCell.FormulaR1C1 = "PCTYM"
Range("D1").Select
ActiveCell.FormulaR1C1 = "PFC"
Range("B1").Select
ActiveCell.FormulaR1C1 = "PACCT"

Range("J1").Select
ActiveCell.FormulaR1C1 = "PPRTCP"
Range("E1").Select
ActiveCell.FormulaR1C1 = "PSUBTY"
Range("H1").Select
ActiveCell.FormulaR1C1 = "PSBUS"
Range("H2").Select
ActiveCell.FormulaR1C1 = "0"
Selection.AutoFill Destination:=Range("H2:H23"), Type:=xlFillDefault
Range("H2:H23").Select
Columns("I:I").Select
Range("I240").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I1").Select
ActiveCell.FormulaR1C1 = "PBS"
Range("I2").Select
ActiveCell.FormulaR1C1 = "1"
Selection.AutoFill Destination:=Range("I2:I23"), Type:=xlFillDefault
Range("I2:I23").Select

Again sorry for the format of the code.

It seems like you have at least 3 separate questions in here. Can you take the [tour] and read up on [ask], then try re-framing your question to address only one particular problem (it's OK to ask multiple questions, separately, but generally could avoid combining 3 or 4 separate problems into a single question). — David Zemens, Aug 01 '18 at 13:12
[How to avoid using Select/Activate](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — David Zemens, Aug 01 '18 at 13:12
[How to find the last used cell in a range/column/sheet](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) — David Zemens, Aug 01 '18 at 13:13
[For...Next](https://msdn.microsoft.com/en-us/VBA/Language-Reference-VBA/articles/fornext-statement) loop documentation — David Zemens, Aug 01 '18 at 13:14
[For Each ... Next](https://msdn.microsoft.com/en-us/VBA/Language-Reference-VBA/articles/for-eachnext-statement) loop documentation — David Zemens, Aug 01 '18 at 13:14
So, you need to 1) Loop over each Worksheet in the `ActiveWorkbook.Worksheets` collection, and 2) modify your code in such a way that it finds the appropriate "last row" for any column/etc that you need to operate against. For bonus points: 3) refactor the code to avoid `Select` and `ActiveCell` :) — David Zemens, Aug 01 '18 at 13:20

score 1 · Accepted Answer · answered Aug 01 '18 at 13:59

I cleaned up your code a bit, and put it into a sub which takes a worksheet as its argument. I then made another sub which loops through all the worksheets of the workbook the code resides in, and makes the changes specified.

Option Explicit

Sub test()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        Call fix_stuff_in_the_sheet(ws)
    Next ws
End Sub

Sub fix_stuff_in_the_sheet(ws As Worksheet)

    ws.Range("G1").FormulaR1C1 = "PSTRIK"
    ws.Range("A1").FormulaR1C1 = "PRECID"
    ws.Range("A2").FormulaR1C1 = "P"
    ws.Range("A2").AutoFill Destination:=Range("A2:A26"), Type:=xlFillDefault
    ws.Range("A2:A26").FormulaR1C1 = "PEXCH"

    ws.Range("C2").FormulaR1C1 = "7"
    ws.Range("C2").AutoFill Destination:=Range("C2:C26"), Type:=xlFillDefault
    ws.Columns("N:O").Delete Shift:=xlToLeft
    ws.Columns("E:E").Delete Shift:=xlToLeft
    ws.Columns("J:J").Delete Shift:=xlToLeft

    ws.Columns("D:D").Delete Shift:=xlToLeft
    ws.Columns("E:E").Cut
    ws.Columns("G:G").Insert Shift:=xlToRight
    ws.Columns("I:I").Cut
    ws.Columns("K:K").Insert Shift:=xlToRight

    ws.Range("I1").FormulaR1C1 = "PQTY"
    ws.Range("G1").FormulaR1C1 = "PCTYM"
    ws.Range("D1").FormulaR1C1 = "PFC"
    ws.Range("B1").FormulaR1C1 = "PACCT"
    ws.Range("J1").FormulaR1C1 = "PPRTCP"

    ws.Range("E1").FormulaR1C1 = "PSUBTY"
    ws.Range("H1").FormulaR1C1 = "PSBUS"
    ws.Range("H2").FormulaR1C1 = "0"
    ws.Range("H2").AutoFill Destination:=Range("H2:H23"), Type:=xlFillDefault
    ws.Range("I240").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    ws.Range("I1").FormulaR1C1 = "PBS"
    ws.Range("I2").FormulaR1C1 = "1"
    ws.Range("I2").AutoFill Destination:=Range("I2:I23"), Type:=xlFillDefault

End Sub

If you don't want the code to be performed on all the sheets in your workbook, you need to figure out a way for the first function to only be called with the sheets you want as an argument.

There is still some superfluous code in the subs I post here, e.g. you first autofill range A2:A26 with the letter P, but then later overwrites that with PEXCH. I'll leave it to you to weed out this though.

Excel VBA Loop?

1 Answers1