Select a dynamic table range on a cheet with 2 tables

[mabelO]

Hi,

I have a spreadsheet with 2 tables being populated unto it.

Table_1 populates first and table_2 starts directly 2 cells after Table_1 ends.

I know that table 1 always starts from range A5. I want to be able to tell where table 1 ends and select the whole range of Table_1 and copy to clipboard.

I have attached a mini visual of what i mean.

 

[My Aswer Is This]

Try this:

Sub Copy_Table()
'Modified  4/28/2021  2:29:28 PM  EDT
Sheets("sheet1").ListObjects("Table1").Range.Copy
End Sub

 

[mabelO]

I am trying to add the table but i keep getting the subscript out of range error.

Lastrow = Sheet1.Range("C" & Rows.Count).End(xlUp).Row

ThisWorkbook.Sheets("Sheet1").ListObjects.Add(xlSrcRange, Range("A4:C" & Lastrow), , xlYes).Name = _

What am i doing wrong please?

 

[My Aswer Is This]

Do not understand: I am trying to add the table

In previous post you said:
and copy to clipboard.

So tell me again what your attempting to do.

And any time your dealing with a Excel Table we need the name of the Table.

Are you really dealing with a Table?
A Table is a define range on a sheet

 

[mabelO]

Do not understand: I am trying to add the table

In previous post you said:
and copy to clipboard.

So tell me again what your attempting to do.

And any time your dealing with a Excel Table we need the name of the Table.

Are you really dealing with a Table?
A Table is a define range on a sheet

It is not a table yet, it is a dynamic range of data on an excel sheet that i am trying to convert to a define.

I am downloading Mutiple HTML tables to excel using:

 'Loop Through Each Table and Download it to Excel in Proper Format
    For Each Tab1 In HTML_Content.getElementsByTagName("table")
        With HTML_Content.getElementsByTagName("table")(iTable)
            For Each Tr In .Rows
                For Each Td In Tr.Cells
                    '[S]S[/S]heet1.Cells(iRow, iCol).Select
                    [S]S[/S]heet1.Cells(iRow, iCol) = Td.innerText
                    iCol = iCol + 1
                Next Td
                iCol = Column_Num_To_Start
                iRow = iRow + 1
            Next Tr

        End With

        iTable = iTable + 1
        iCol = Column_Num_To_Start
        iRow = iRow + 1
    Next Tab1

I want to define each table range after they finish populating before it the next table range populates by inserting the below code just after the "End with" in the above code:

Lastrow = [S]S[/S]heet1.Range("A" & Rows.Count).End(xlUp).Row
        
       '[S]S[/S]heet1.Range("A4:C" & Lastrow).Select
       
       ThisWorkbook.Sheets("v").ListObjects.Add(xlSrcRange, Range("A4:C13" & Lastrow), , xlYes).Name = _
"Table1"

But i am getting the Subscript out of range error.

 

[My Aswer Is This]

It is not a table yet, it is a dynamic range of data on an excel sheet that i am trying to convert to a define.

I am downloading Mutiple HTML tables to excel using:

 'Loop Through Each Table and Download it to Excel in Proper Format
    For Each Tab1 In HTML_Content.getElementsByTagName("table")
        With HTML_Content.getElementsByTagName("table")(iTable)
            For Each Tr In .Rows
                For Each Td In Tr.Cells
                    '[S]S[/S]heet1.Cells(iRow, iCol).Select
                    [S]S[/S]heet1.Cells(iRow, iCol) = Td.innerText
                    iCol = iCol + 1
                Next Td
                iCol = Column_Num_To_Start
                iRow = iRow + 1
            Next Tr

        End With

        iTable = iTable + 1
        iCol = Column_Num_To_Start
        iRow = iRow + 1
    Next Tab1

I want to define each table range after they finish populating before it the next table range populates by inserting the below code just after the "End with" in the above code:

Lastrow = [S]S[/S]heet1.Range("A" & Rows.Count).End(xlUp).Row
       
       '[S]S[/S]heet1.Range("A4:C" & Lastrow).Select
      
       ThisWorkbook.Sheets("v").ListObjects.Add(xlSrcRange, Range("A4:C13" & Lastrow), , xlYes).Name = _
"Table1"

But i am getting the Subscript out of range error.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[Fluff]

How about

iTable = iTable + 1
Lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Sheet1.ListObjects.Add(xlSrcRange, Range("A4:C" & Lastrow), , xlYes).Name = "Table"&iTable

 

[mabelO]

Thank you for your response but that did not work either. I have taken a different direction with the project but i would really love to learn how to populate and define tables on excelsheets through VBA in the future..

 

[My Aswer Is This]

To create a Excel Table using Vba you could use something like this:

Select the Range where you want the Table.
And then Run this Script:

Sub Make_My_Table()
'Modified  4/30/2021  1:05:19 PM  EDT
On Error GoTo M
Dim ans As String
ans = InputBox("Enter Name for new Table")
ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = ans
Exit Sub
M:
MsgBox "We had a problem" & vbNewLine & "Maybe you used a Improper Name." & vbNewLine & "You used " & ans _
& vbNewLine & "This may not be a proper name"
End Sub

 

[Fluff]

that did not work either

It's always best to give us more information as "didn't work" doesn't tell us much.
That said I missed a bit from the code & it should be

iTable = iTable + 1
Lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Sheet1.ListObjects.Add(xlSrcRange, Sheet1.Range("A4:C" & Lastrow), , xlYes).Name = "Table" & iTable