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.

```
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
```

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel