I have a script that takes data from a parent worksheet (data import) and distributes it to the appropriate child worksheets. Each of those worksheets contains the same table just with different data. Here is the script that places the new data at the end of each respective table.
The above code works beautifully to add the new data to the bottom of each sheet's table as seen here:
What I would like to do is then loop through the sheets containing tables and resize them to encompass the new data. Here is my attempt at that code:
This code works, but only on the active sheet. Clearly looping through the sheets does not make them active and that is my hang up. How do I get this to run on all the worksheets except the last two? Ideally, I think it would be nice if all of this could be accomplished in one macro. Any help is appreciated. Thank you in advance!
VBA Code:
Sub Dispatch()
rng = shtWF.Cells(1, 1).CurrentRegion
On Error Resume Next
For rw = 1 To UBound(rng)
rRng = Right(rng(rw, 6), 4)
Sheets(rRng).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, UBound(rng)).Value = shtWF.Cells(rw, 1).Resize(1, UBound(rng)).Value
Next rw
On Error GoTo 0
End Sub
What I would like to do is then loop through the sheets containing tables and resize them to encompass the new data. Here is my attempt at that code:
VBA Code:
Sub TblResize()
Dim i As Integer
Dim tbl As ListObject
Dim lrw As Long
lrw = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Worksheets.Count - 2
For Each tbl In ActiveSheet.ListObjects
tbl.Resize Range("A3", "t" & lrw)
Next
Next i
End Sub