The Power Loon
New Member
- Joined
- Feb 7, 2020
- Messages
- 34
- Office Version
- 365
- Platform
- Windows
I am looking to create a table in each sheet in a workbook. Each sheet has data in columns A through R, with a variable number of rows. The header would be in row 1.
I was able to cobble together the code below, but am getting an error on the "sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select" line.
I'm hoping you could help fix this or recommend something else? Let me know if I can provide any further info to assist in this.
I was able to cobble together the code below, but am getting an error on the "sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select" line.
I'm hoping you could help fix this or recommend something else? Let me know if I can provide any further info to assist in this.
VBA Code:
Sub CreateTables()
Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
For Each sht In ThisWorkbook.Sheets
With sht
Set StartCell = Range("A1")
LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column
sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select
End With
Next sht
End Sub