CODEWITHSUNDEEP
vbaexcel
user1283776
user1283776

Reputation: 21764

VBA PasteSpecial operation fails?

I try to cut a row from one listobject into another listobject:

'Create new row
Dim lNewRowNumber As Long
lNewRowNumber = loTrgt.DataBodyRange.Row + loTrgt.ListRows.Count
trgtWorkSheet.Rows(lNewRowNumber).EntireRow.Insert

'Cut old row
Dim lCutRow As Long
Dim lCutStartColumn As Long
Dim lCutEndColumn As Long
lCutRow = t.Row
lCutStartColumn = loSrc.Range.Column
lCutEndColumn = loSrc.Range.Column + loSrc.ListColumns.Count - 1

'Paste row
Dim lPasteRow As Long
Dim lPasteColumn As Long
lPasteRow = lNewRowNumber
lPasteColumn = loTrgt.Range.Column

t.Worksheet.Range(t.Worksheet.Cells(lCutRow, lCutStartColumn), t.Worksheet.Cells(lCutRow, lCutEndColumn)).Cut
trgtWorkSheet.Cells(lPasteRow, lPasteColumn).PasteSpecial xlPasteAll

The makro stops on the last row of the code pasted above. It tells me that the paste operation of the range object failed. Any idea why this might be? I don't think it has to do with the listobjects, because I seem to get the problem when trying to cut and paste other rows as well using the code above.

Upvotes: 0

Views: 166

Answers (1)

Jason Faulkner
Jason Faulkner

Reputation: 6558

Since you are using Cut, try Insert instead of PasteSpecial. This is synonymous with "Insert Cut Cells" which you see when you are using the Excel interface:

trgtWorkSheet.Cells(lPasteRow, lPasteColumn).Insert

Also - make sure the cell you are pasting/inserting to is not in the range which is being cut.

Upvotes: 1

Related Questions