CODEWITHSUNDEEP
vbaexcel
worldCurrencies
worldCurrencies

Reputation: 467

How to Fill Column Loop? VBA Excel

So i would like to make a loop that autofills the same column throughout all worksheets. I recorded the macro but obviously that is a lot of code. I tried to use a loop, but some of the variables i want to autofill with have numbers at the end (e.g. "ULA2"), And so with auto fill it would make the entire column ULA2, ULA3, ULA4,... How can I correct this? Here is my code:

Sheets("ULA2").Select
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "ULA2"
    Range("F4").Select
    ActiveCell.FormulaR1C1 = "ULA2"
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "ULA2"
    Range("F6").Select
    ActiveCell.FormulaR1C1 = "ULA2"
    Range("F7").Select
    ActiveCell.FormulaR1C1 = "ULA2"
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "ULA2"
    Range("F9").Select
    ActiveCell.FormulaR1C1 = "ULA2"
    Range("F10").Select
    ActiveCell.FormulaR1C1 = "ULA2"
    Range("F11").Select

LastRow = Cells(Rows.Count, "F").End(xlUp).Row
Range("F2").AutoFill Destination:=Range("F2:F" & LastRow), Type:=xlFillDefault

Sheets("BO").Select
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "BO"
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F"), Type:=xlFillDefault
    Range("F2:F").Select

Upvotes: 1

Views: 1849

Answers (2)

Marcucciboy2
Marcucciboy2

Reputation: 3263

This should do the trick. It will loop through all of your sheets and set row F (after the header and down to the bottom) equal to the name of the sheet

Dim i As Long
Dim LastRow As Long

For i = 1 To Worksheets.count
    With Sheets(i)
        LastRow = .Cells(Rows.count, "F").End(xlUp).row
        .Range("F2:F" & LastRow).Value2 = .Name
    End With
Next i

Upvotes: 1

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

If you want to do this on all worksheets you can use the following:

For Each mySht In Application.Worksheets
    mySht.Range("F2:F" & mySht.UsedRange.Row + mySht.UsedRange.Rows.Count - 1).Value = mySht.Name
Next mySht

Upvotes: 1

Related Questions