Search code examples
excelvbaforeachautofill

VBA Excel cycle through sheets, find last row in table and autofill with data

I need a script that cycles through all the sheets in a workbook and adds 2 (or more) columns at the end of the table of each sheet (except the first sheet). In this case I have a YES and NO column to be added.

The added columns YES and NO need to be autofilled all the way down to the end with YES or NO like in the example here below.

enter image description here


Sub LoopSheetsAddDataFilledColumns()

Dim LastRow As Long
Dim LastCol As Long
Dim iRow As Long
Dim ws As Worksheet

For Each ws In Worksheets

Set ws = Sheets(2)

With ws
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row 
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

    .Columns(LastCol + 1).EntireColumn.Insert
    .Cells(1, LastCol + 1).Value = "YES"
    .Columns(LastCol + 2).EntireColumn.Insert
    .Cells(1, LastCol + 2).Value = "NO"

End With

Next

End Sub

The issue is that:

  • this script adds only a few columns with YES and NO on the first sheet,
  • it doesn't fill the columns all the way to the end,
  • It doesn't make exceptions for particular sheets,

Can someone please help update this script? Thank you in advance.

Solution

  • Try this modification of your code

    Sub LoopSheetsAddDataFilledColumns()

Dim LastRow As Long
Dim LastCol As Long
Dim iRow As Long
Dim ws As Worksheet

For Each ws In Worksheets

If ws.Name <> Sheets(1).Name Then
With ws
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

    .Columns(LastCol + 1).EntireColumn.Insert
    .Columns(LastCol + 2).EntireColumn.Insert
        
    .Range(.Cells(1, LastCol + 1), .Cells(LastRow, LastCol + 1)).Value = "YES"
    .Range(.Cells(1, LastCol + 2), .Cells(LastRow, LastCol + 2)).Value = "NO"

End With
End If
Next

End Sub