CODEWITHSUNDEEP
excelvba
mps.declade
mps.declade

Reputation: 33

Excel VBA getting and using Name of Table

I got stuck at a problem with Excel VBA.

I am supposed to do the kinda easy task of copy/paste a variable range of cells from "sheet2" into the same range in "sheet1".

500 Rows like in my code is far too much, but I tried it this way, to "catch" the variable aspect.

The tricky part is, that the range in "sheet1" is a table(which gets created from TFS).

Sub CopyP()


Sheets("Sheet1").Range("B3:F500").Value = Sheets("Sheet2").Range("B3:F500").Value

SheetObject.ListObjects (ListObjectName)
Range("NAME OF TABLE[Iteration Path]").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

The [Iteration Path] is a column name of my table, i want to check in this column/with this parameter, if the "row" is empty.

I know the code is far from being good or clean but the table is giving me a hard time copying.

With this code I got another problem: the table gets created from TFS, no problem to copy into that, BUT the name of the table is variable(seems like TFS creates the name), so unless I put the name manually in the code, the "program" cant execute, because of missing range.

Didn't find a way to get a return of the table name somehow.

But I think I am just following the wrong path overall, maybe someone can be bring me on the right track.

My other Idea is to Iterate through the Rows in Sheet 2 to fetch just as much data is needed and then copy them with an iteration into the table. But i guess I would be the same problem with the table-name there.

Every information I find using google , talks about tables where the user can "name" the table. In my case I cant, so I have to work with the name TFS uses for my table.

Upvotes: 3

Views: 11321

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

Further to my comments below your question, I just typed this in notepad. Please amend it to suit your need.

This will give you the names of all tables in the activesheet. If there are multiple tables then you will get multiple names. (UNTESTED AS POSTING FROM PHONE)

Sub sample()
    Dim objLB As ListObject, TableName As String

    For Each objLB In ActiveSheet.ListObjects
        TableName = objLB.Name
        Exit For
    Next

    Range(TableName & "[Iteration Path]").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Upvotes: 3

Related Questions