Trying to resize a table to match the number of rows in another table

[sspatriots]

I can't seem to get my second table's number of rows to match the first table that I'm referencing. Below is what I have so far, however, I keep getting a "Compile error: Type mismatch". Any suggestions would be greatly appreciated. Thanks, SS

    Dim wb1 As Workbook
    Dim ws1, ws2 As Worksheet
    Dim tb1, tb2 As ListObject
    Dim rng1, rng2, rng3 As Range
    Dim cl As Range
    Dim numRows As Long
'    Dim numRows2 As Long
    
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    '    .CutCopyMode = False
    '    .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
    
    Set wb1 = ThisWorkbook
    Set ws1 = wb1.Worksheets("2024")
    Set ws2 = wb1.Worksheets("PO File Paths")
    Set tb1 = ws1.ListObjects("Table4711")
    Set tb2 = ws2.ListObjects("POHLINKCNV")
    Set rng1 = Range("POHLINKCNV[Full PO '#]")
    Set rng2 = Range("POHLINKCNV[Hyperlink]")
    
    Sheets("PO File Paths").Select

    With Range("FilePathRange")
        .ClearContents
        .ClearFormats
        .ClearHyperlinks
    End With

    numRows = tb1.ListRows.Count
    Set rng3 = tb2.Resize(numRows, 2)
    ws2.tb2.Resize rng3

End Sub

 

[NdNoviceHlp]

Hi sspatriots. Not real sure if this will fix your error but it won't hurt.
This...

Dim ws1, ws2 As Worksheet
Dim tb1, tb2 As ListObject
Dim rng1, rng2, rng3 As Range

is not the same as....

Dim ws1 As Worksheet, ws2 As Worksheet
Dim tb1 As ListObject, tb2 As ListObject
Dim rng1 As Range, rng2 As Range, rng3 As Range

The way you have declared the variables leaves the initially declared variables (ie. tb1) as Variant which XL may decide to define as something other than what you had intended which will result in the type mismatch error that you are getting. HTH. Dave

 

[sspatriots]

Hi sspatriots. Not real sure if this will fix your error but it won't hurt.
This...

Dim ws1, ws2 As Worksheet
Dim tb1, tb2 As ListObject
Dim rng1, rng2, rng3 As Range

is not the same as....

Dim ws1 As Worksheet, ws2 As Worksheet
Dim tb1 As ListObject, tb2 As ListObject
Dim rng1 As Range, rng2 As Range, rng3 As Range

The way you have declared the variables leaves the initially declared variables (ie. tb1) as Variant which XL may decide to define as something other than what you had intended which will result in the type mismatch error that you are getting. HTH. Dave

Perfect. Thank you. I'll go back and change them. I always thought they were one in the same.

 

[John_w]

Like this:

Sub Your_Code()

    Dim tb1 As ListObject, tb2 As ListObject

    'Include your code to declare and define ws1 and ws2 here
  
    Set tb1 = ws1.ListObjects("Table4711")
    Set tb2 = ws2.ListObjects("POHLINKCNV")
    Resize_Table tb2, tb1.ListRows.Count

End Sub

Private Sub Resize_Table(table As ListObject, numRows As Long)

    With table.DataBodyRange
        If .Rows.Count > numRows Then
            'Optional - clear excess rows at bottom
            .Item(numRows + 1, 1).Resize(.Rows.Count - numRows, .Columns.Count).Clear
        End If
        'Resize table with 1 header row and specified number of rows
        table.Resize .Offset(-1).Resize(1 + numRows)
    End With

End Sub

 

[sspatriots]

It's working now. Below is what I ended up with after I made the changes suggested and tweaked that last few lines a little:

Sub CreateHyperlinks2024()

    Dim wb1 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim tb1 As ListObject, tb2 As ListObject
    Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range, rng5 As Range
    Dim cl As Range
    Dim numRows As Long
    
    Set wb1 = ThisWorkbook
    Set ws1 = wb1.Worksheets("2024")
    Set ws2 = wb1.Worksheets("PO File Paths")
    Set tb1 = ws1.ListObjects("Table4711")
    Set tb2 = ws2.ListObjects("POHLINKCNV")
    Set rng1 = Range("Table4711[#All]")
    Set rng2 = Range("POHLINKCNV[#All]")
    Set rng3 = Range("POHLINKCNV[Full PO '#]")
    Set rng4 = Range("POHLINKCNV[Hyperlink]")
    Set rng5 = Range("Table4711[PO'#]")

'    rng5.Select
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    '    .CutCopyMode = False
    '    .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With

    Sheets("PO File Paths").Select
    
'    POFilePaths2024
    
    With Range("FilePathRange")
        .ClearFormats
        .ClearHyperlinks
    End With
    
    numRows = rng1.SpecialCells(xlCellTypeLastCell).Row - 1
    Set rng2 = rng2.Resize(numRows)
    tb2.Resize rng2

Thanks, SS

 

[sspatriots]

Like this:

Sub Your_Code()

    Dim tb1 As ListObject, tb2 As ListObject

    'Include your code to declare and define ws1 and ws2 here
 
    Set tb1 = ws1.ListObjects("Table4711")
    Set tb2 = ws2.ListObjects("POHLINKCNV")
    Resize_Table tb2, tb1.ListRows.Count

End Sub

Private Sub Resize_Table(table As ListObject, numRows As Long)

    With table.DataBodyRange
        If .Rows.Count > numRows Then
            'Optional - clear excess rows at bottom
            .Item(numRows + 1, 1).Resize(.Rows.Count - numRows, .Columns.Count).Clear
        End If
        'Resize table with 1 header row and specified number of rows
        table.Resize .Offset(-1).Resize(1 + numRows)
    End With

End Sub

I actually like this better than what I ended up with. Keeping this one in my back pocket because it makes it option to clear the excess rows at the bottom...