Resize a table or........?

With this code i copy 8 lines to a table. The table is +/- 35 columns wide.

Dim y As Integer
    For y = 0 To 7
    nRow = shP.Range("B" & shP.Rows.Count).End(xlUp).Offset(1).Row
        shP.Cells(nRow, 2) = shJ.Cells(7, 13 + y).Value
        shP.Cells(nRow, 3) = 41
        shJ.Range("$D$9").AutoFilter Field:=1, Criteria1:="=40", _
        Operator:=xlOr, Criteria2:="=41"
        shJ.Range(Cells(10, 13 + y), Cells(shJ.Cells(Rows.Count, 3).End(xlUp).Row, 13 + y)).Copy
        shP.Cells(nRow, 4).PasteSpecial Paste:=xlPasteValues, Transpose:=True
    

        shP.Cells(nRow + 1, 2) = shJ.Cells(7, 13 + y).Value
        shP.Cells(nRow + 1, 3) = 42
        shJ.Range("$D$9").AutoFilter Field:=1, Criteria1:="=40", _
        Operator:=xlOr, Criteria2:="=42"
        shJ.Range(Cells(11, 13 + y), Cells(shJ.Cells(Rows.Count, 3).End(xlUp).Row, 13 + y)).Copy
        shP.Cells(nRow + 1, 4).PasteSpecial Paste:=xlPasteValues, Transpose:=True
    Next y

But because there are cell with a Matrixformule witch more then one cell the code gives a error.

To prefend to copy in a table and get this error i stop the table from expending with this.

Application.AutoCorrect.AutoExpandListRange = False

But when i use

Application.AutoCorrect.AutoExpandListRange = True

the table don't expand on it own.

How to expand a table or copy matrix formulas with more then one cell to a table?

The table in in sheet "PKB" and the table is called "PKB".

Yep that is what i want.
I thought i had a solution.

    With Worksheets("PKB").ListObjects("PKB")
        .Resize .Range(1, 1).CurrentRegion
    End With

But somehow ".Resize .Range(1, 1).CurrentRegion" gives a error when the sheet is protected. Even when UserInterFaceOnly:=True

So i still have no answer to the problem.

This is the sheet with the tabel. The question is how to make the last 16 rows part of the table with a VBA code when the sheet is protected.

It is a growing list. Every day 48 more lines!

Changes

LR = Cells(rows.count,1).End(xlUp).Row

in

LR = Cells(rows.count,2).End(xlUp).Row

and it is working like a charm!

Tanks

Maybe in the near future someone adds a column. So i like to count columns to.
Where do i put de LC?

Set ShI = Sheets("PKB")

LR = ShI.Cells(Rows.Count, 2).End(xlUp).Row
LC = ShI.Cells(2, Columns.Count).End(xlToLeft).Column
ShI.ListObjects("PKB").Resize Range("$B$5" & LC & LR)

This in not working. I don't see why. Never got stuk on something so small in VBA before.

Sub test()
Set ShP = Sheets("PKB")
LR = ShP.Cells(Rows.Count, 2).End(xlUp).Row
LC = ShP.Cells(5, Columns.Count).End(xlToLeft).Column
ShP.ListObjects("PKB").Resize Range("$B$5", Cells(LR, LC))

End Sub

Not when the sheet is protected? Unlock the sheet is not ideal.