VBA - Create table in each sheet

[The Power Loon]

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.

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

 

[Joe4]

It looks like you are confused on how to use the "With" statement.

When you use "With" with a worksheet, it means that you do not need to qualify every range within the "With" block with the sheet name.
HOWEVER, that only works if you put a period in front of the range, i.e.
with this:

With sht
Set StartCell = Range("A1")

the "StartCell" will ALWAYS be Range("A1") on the original sheet you were on when you called this code to run, even as you go through each sheet.
To get it to apply to Range("A1") on the Sheet you are currently looped on, you need to do this:

With sht
Set StartCell = .Range("A1")

And not everywhere else within this WITH block, you do not need to preface the range with "sht" (it defeats the purpose of using "With").
So all you references like this:

sht.Cells

can be simplified to this:

.Cells

(simply replace each instance of "sht." with ".")

 

[igold]

I am not going to try to apply this to your code but something like this may help you...

Sub LastCellWithData()
  Dim LastCell As Range
  Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column)
      MsgBox "Last Cell With Data Is: " & LastCell.Address(False, False)
End Sub

 

[The Power Loon]

It looks like you are confused on how to use the "With" statement.

When you use "With" with a worksheet, it means that you do not need to qualify every range within the "With" block with the sheet name.
HOWEVER, that only works if you put a period in front of the range, i.e.
with this:

With sht
Set StartCell = Range("A1")

the "StartCell" will ALWAYS be Range("A1") on the original sheet you were on when you called this code to run, even as you go through each sheet.
To get it to apply to Range("A1") on the Sheet you are currently looped on, you need to do this:

With sht
Set StartCell = .Range("A1")

And not everywhere else within this WITH block, you do not need to preface the range with "sht" (it defeats the purpose of using "With").
So all you references like this:

sht.Cells

can be simplified to this:

.Cells

(simply replace each instance of "sht." with ".")

You are correct, I have absolutely no idea how "With" works. All of my understanding comes from observing how code I've found online works, so I have some unfortunate gaps with some of the functions.

I followed your advice and am still getting an error on that same line. Did I misunderstand something?

        With sht
            Set StartCell = .Range("A1")
  
        LastRow = .Cells(.Rows.Count, StartCell.Column).End(xlUp).Row
        LastColumn = .Cells(StartCell.Row, .Columns.Count).End(xlToLeft).Column
        .Range(StartCell, .Cells(LastRow, LastColumn)).Select

 

[Joe4]

You are correct, I have absolutely no idea how "With" works. All of my understanding comes from observing how code I've found online works, so I have some unfortunate gaps with some of the functions.

I followed your advice and am still getting an error on that same line. Did I misunderstand something?

        With sht
            Set StartCell = .Range("A1")
 
        LastRow = .Cells(.Rows.Count, StartCell.Column).End(xlUp).Row
        LastColumn = .Cells(StartCell.Row, .Columns.Count).End(xlToLeft).Column
        .Range(StartCell, .Cells(LastRow, LastColumn)).Select

OK, just looping through sheets does not automatically activate or select them. Usually, this isn't an issue because you often do not need to activate or select a sheet in order to do calculations against it.
However, if you can only select a cell on a sheet if that is the active sheet. So, if you are looping through the sheets, and want to select a cell on each one, you need to select/activate the sheet before selecting the cell on it, i.e.

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 = .Cells(.Rows.Count, StartCell.Column).End(xlUp).Row
            LastColumn = .Cells(StartCell.Row, .Columns.Count).End(xlToLeft).Column
            .Activate
            .Range(StartCell, .Cells(LastRow, LastColumn)).Select
        End With
    Next sht

End Sub

 

[The Power Loon]

OK, just looping through sheets does not automatically activate or select them. Usually, this isn't an issue because you often do not need to activate or select a sheet in order to do calculations against it.
However, if you can only select a cell on a sheet if that is the active sheet. So, if you are looping through the sheets, and want to select a cell on each one, you need to select/activate the sheet before selecting the cell on it, i.e.

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 = .Cells(.Rows.Count, StartCell.Column).End(xlUp).Row
            LastColumn = .Cells(StartCell.Row, .Columns.Count).End(xlToLeft).Column
            .Activate
            .Range(StartCell, .Cells(LastRow, LastColumn)).Select
        End With
    Next sht

End Sub

Thank you. I appreciate your help on this.

 

[Joe4]

You are welcome.