I have a problem with VBA. I am fairly new to it so some help would be appreciated :)
What I want to achieve:
If a child table has a row filled, i want it to add a new row.
What I have tried
because i have 10 parent tables underneath eachother I cannot use Cells(Cells.Rows.Count, "E").End(xlUp).Row
because it will only count the last parent table
So I tried doing it with listobjects, But whenever i try to add a listobject:
With Worksheets("Uitwendige scheidingen")
.ListObject.Add(Range("F" & NextRow + 25)).Name = "tbl_schuindak_orientatie" & Rij
End With
(Rij = the parent table) (NextRow = start position of the parent table)
I get the error 438
Note
I do not have any listobjects added in the sheet before. so the parent tables are basicly 'fake' tables and not listobjects
First of, you have to .Add
a ListObject
to the ListObjects
collection. Furthermore, you can't set a new ListObject
through it's Name
property. You first have to initialize it with a proper source range. Then rename it after. As a simplistic example:
Sub Test()
Dim nxt As Long
Dim tbl As ListObject
With ThisWorkbook.Worksheets("Sheet1")
For x = 1 To 10 Step 3
Set tbl = .ListObjects.Add(xlSrcRange, Source:=.Range(.Cells(x, 1), .Cells(x + 1, 1)), XlListObjectHasHeaders:=xlYes)
tbl.Name = "Table" & x
Next x
End With
End Sub
Now apply this logic to your situation.
Note: See how xlYes
is applied to tell the XlListObjectHasHeaders
parameter in this example it needs to take into consideration my source ranges have headers. Set to xlNo
if yours don't.
Thank you very much! This will help me a lot!