Excel VBA to Format as Table and then Change Orientation on all Sheets

Question

I am trying to get the code below to make a table for only the columns with data and to do so for every sheet in the workbook, and then after change the orientation of all sheets to landscape but its doing 2 things weird:

1. Not looping through sheets.

2. Making all columns into the table not just those with data.

Can it be done to only look for columns with data and format those into a table?

Sub Format_As_Table()

Dim tbl As ListObject
Dim rng As Range
Dim sh As Worksheet

For Each sh In ThisWorkbook.Sheets
    Set rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
    tbl.TableStyle = "TableStyleMedium15"
Next sh
Application.ScreenUpdating = False
Call Orientation

End Sub

'======================================================================

Sub Orientation()

Dim sh As Worksheet

For Each sh In ThisWorkbook.Sheets
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
    End With
Next sh
Application.ScreenUpdating = False

End Sub

Answer 1

You can combine both Subs into one.

Your original code was looping through the sheets, but the objects underneath are not fully qualified with the current sh of the loop. Instead they reference the ActiveSheet, which is whatever sheet was active when you run this code.

Once you add With sh, and underneath you nest your objects with a . as prefix, all objects will be fully qualified with sh. For example:

Set Rng = .Range(.Range("A1"), .Range("A1").SpecialCells(xlLastCell))

Note: Using SpecialCells(xlLastCell) to get the last cell might not be the most reliable way. Read HERE on different methods and advantages.

Code

Option Explicit

Sub Format_As_Table_and_Orientation()

Dim Tbl As ListObject
Dim Rng As Range
Dim sh As Worksheet

Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Sheets
    With sh
        Set Rng = .Range(.Range("A1"), .Range("A1").SpecialCells(xlLastCell))
        Set Tbl = .ListObjects.Add(xlSrcRange, Rng, , xlYes)
        Tbl.TableStyle = "TableStyleMedium15"

        .PageSetup.Orientation = xlLandscape
    End With

Next sh
Application.ScreenUpdating = True

End Sub