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 How to 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
– David Zemens, Commented Aug 1, 2018 at 13:12

eirikdaude · Accepted Answer · 2018-08-01 13:59:38Z

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.

Collectives™ on Stack Overflow

Excel VBA Loop?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related