Reputation: 33
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
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