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