2

The setup: I have an excel doc with a form for entering data, the data for that form is entered in a table to allow for easy entering of multiple rows of data. At least i thought so.

So right now i am trying to select the table to insert its data in the appropriate places. My question, i think, is this: do i select one table row at a time, or the whole table and deal with each row separately. And how do i do that?

I tried Sheets("Form").Range("dataForm[#ALL]").Select and several variations thereof and none worked.

If i select the table as a whole i need to be able to deal with each row seperately and if i select each row individually i need to be able to be able to start at the top of the table as the data must be in order.

Any ideas?

EDit: To add detail. I have a form as stated above and its data must be inserted in different tables dependent on the value of certain cells in the form. For ease of discussion we will name that cell type, it has three possible values, as defined in a dropdown. Those values are income, expense and transfer. Based on those values we decide which table to add the data to. Income to the income table expense to the expense, etc.

So what i am trying to do is select as many rows as there are and insert each one into the correct table. The sorting is slightly more complicated than i have explained but if i can figure out the initial sort then it should be simple to sort it a few more times.

Community
  • 1
  • 1
SpeedCrazy
  • 505
  • 2
  • 7
  • 19
  • Are you deleting the rows from the source table after they're inserted in the appropriate table? – Doug Glancy Apr 17 '12 at 03:37
  • deleting all but one and clearing that one. If i delete them all i lose the table and the data validation settings(my dropdowns). – SpeedCrazy Apr 17 '12 at 03:40

2 Answers2

5

This should help answer your questions.

Sub TableStuff()
Dim lo As Excel.ListObject
Dim loRow As Excel.ListRow
Dim i As Long

Set lo = ActiveSheet.ListObjects(1)
With lo
'this is the address of the whole table
Debug.Print .Range.Address
    For i = 1 To 10
        Set loRow = .ListRows.Add(i)
        loRow.Range.Cells(1).Value = "test" & i
    Next i
Debug.Print .Range.Address
'address of data rows
Debug.Print .DataBodyRange.Address
End With
End Sub

I have two posts on my blog about tables. A recent one might also provide some insights.

EDIT: Based on comments below and edit to OP:

This assumes two tables on Activesheet, tblSource and tblIncome. It filters the source table to Income, copies copies the visible rows and inserts them at the end of tblIncome. Finally, it deletes the source rows (all but one).

You'll want to add a loop to have it work for the other two categories:

Sub MoveTableStuff()
Dim loSource As Excel.ListObject
Dim loTarget As Excel.ListObject
Dim SourceDataRowsCount As Long
Dim TargetDataRowsCount As Long

Set loSource = ActiveSheet.ListObjects("tblSource")
Set loTarget = ActiveSheet.ListObjects("tblIncome")
With loSource
    .Range.AutoFilter Field:=1, Criteria1:="income"
    SourceDataRowsCount = .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Count
End With
With loTarget
    TargetDataRowsCount = .DataBodyRange.Rows.Count
    .Resize .Range.Resize(.Range.Rows.Count + SourceDataRowsCount, .Range.Columns.Count)
    loSource.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
    .DataBodyRange.Cells(TargetDataRowsCount + 1, 1).PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False
End With
With loSource
    .Range.AutoFilter
    .DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete
End With
End Sub
Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • Clarification please, what does Listobject(1) refer to? Do i put the table name in there? Thanks for your help, i will be sure to check out your blog – SpeedCrazy Apr 17 '12 at 02:14
  • Yes, ListObject is the VBA name for a table. 1 refers to the first table on the sheet. Listobjects("Table1"), or whatever the name is, will also work. – Doug Glancy Apr 17 '12 at 02:17
  • It just adds ten rows to the table. How would i turn that around? I need some thing like(i now this is not correct code) `loRow.Range.Row.EntireRow.Copy` Is that possible? And then before breaking and going to the next row delete that row? – SpeedCrazy Apr 17 '12 at 02:30
  • Yes it just adds ten rows. Your original question was general, so I was trying to give you a general sense of how to manipulate rows. Anyways, yes you could say "loRow.Range.Copy" and then "loRow.Delete." If you edit your original question to add the code you've got so far, and a description of what you are trying to do that would be great. – Doug Glancy Apr 17 '12 at 03:19
  • Thanks for all your help so far Doug, i added more detail in OP. – SpeedCrazy Apr 17 '12 at 03:30
1

If you already gave a name to your table, I have a function to get its full data range:

Public Function GetTableByName(ByVal ws As Worksheet, ByVal tbName As String) As Range

    Dim lObj As ListObject

    For Each lObj In ws.ListObjects
        If Trim(UCase(lObj.Name)) = Trim(UCase(tbName)) Then
            Set GetTableByName = lObj.DataBodyRange
            Exit Function
        End If
    Next lObj

End Function
Alpha
  • 2,372
  • 3
  • 21
  • 23