Copy Paste From Table to Table

[pgspringer]

Hello,

I am working on a To-Do list in Excel 2010. I am running into a paste range class invalid error. I am relatively new to VBA working off macros and google.

The part I am having trouble with is moving the completed tasks to another table in the sheet, the code I currently have is this

---

Sub MoveCompleted()
Dim LastRow As Variant
Set LastRow = ActiveSheet.ListObjects("Table710").ListRows.Add

ActiveSheet.ListObjects("Table7").Range.AutoFilter Field:=5, Criteria1:="Completed"
ActiveSheet.ListObjects("Table7").DataBodyRange.Copy
ActiveSheet.ListObjects("Table7").Range.AutoFilter Field:=5
LastRow.Range.PasteSpecial xlPasteValues
End Sub

---

Right now I am not worried about deleting the original data from Table7, I figured I would worry about that later.

Everything works up until paste. My thought is because I am only adding one row and the data copied is potentially more than one, but I do not have the knowledge of how to proceed. Any help would be wonderfully obliged.

Thank You!

Also working on Windows 7 - 64bit.


​

 

[pgspringer]

Well this is what I found:

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


Set loSource = ActiveSheet.ListObjects("Table7")
Set loTarget = ActiveSheet.ListObjects("Table710")
With loSource
    .Range.AutoFilter Field:=5, Criteria1:="Completed"
    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 Field:=5
    .DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete
End With
End Sub

So this is filtering data from table 1, counting how many rows there are, copying it, adding - or resizing I guess, the second table to how many rows were copied, and then pasting the values (with out error because there are rows for the data to go in to), then deleting the others (which I don't quite understand yet) - am I correct?


If someone would like to take the time to help me understand this part:

.DataBodyRange.Cells(TargetDataRowsCount + 1, 1)

Of this:

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

That would be awesome!

Also, Source: excel vba - How to select table rows/complete table? - Stack Overflow

Thanks

 

[pgspringer]

Annnnnd Final Code that moves completed tasks from one table to another, and removing them from the first....:

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

Set loSource = ActiveSheet.ListObjects("Table7")
Set loTarget = ActiveSheet.ListObjects("Table710")
With loSource
    .Range.AutoFilter Field:=5, Criteria1:="Completed"
    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
    .DataBodyRange.SpecialCells(xlCellTypeVisible).ClearContents
        .Range.AutoFilter Field:=5
    .Sort.SortFields.Add _
        Key:=Range("Table7[[#All],[Status]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    .Resize .Range.Resize(.Range.Rows.Count - SourceDataRowsCount, .Range.Columns.Count)
End With
End Sub

 

[rarascon]

Thanks for posting this! I went through a few other sites/links, but this one worked perfectly for what I needed to do!