Forum Discussion

Brass Contributor

Mar 14, 2023

Solved

ListRows.Add.Range (Values only)

Hello all I need to copy the databody range from one table to another but only the values. I would ordinarily use listRows.Add.Range e.g. MySourceTable.DataBodyRAnge.Copy MyDestinatio...

Mar 14, 2023

You're correct - the clipboard gets cleared. Try this:

    Dim NumRows As Long
    Dim i As Long
    NumRows = MySourceTable.ListRows.Count
    For i = 1 To NumRows
        MyDestinationTable.ListRows.Add.Range.Value = MySourceTable.ListRows(i).Range.Value
    Next i

HansVogelaar

MVP

Mar 14, 2023

ITTom365

MySourceTable.DataBodyRAnge.Copy
MyDestinationTable.ListRows.Add.Range.PasteSpecial Paste:=xlPasteValues

ITTom365
Brass Contributor
Mar 14, 2023
Hi,

I thought that but I get the error "the method PasteSpecial of class range failed" .

I have been reading about the error, most seem to occur when the clipboard gets inadvertantly cleared i.e. there is nothing to paste, I dont believe this is the case here ? any further input much appreciated
- HansVogelaar
  MVP
  Mar 14, 2023
  ITTom365
  
  You're correct - the clipboard gets cleared. Try this:
  
  Dim NumRows As Long Dim i As Long NumRows = MySourceTable.ListRows.Count For i = 1 To NumRows MyDestinationTable.ListRows.Add.Range.Value = MySourceTable.ListRows(i).Range.Value Next i
  - ITTom365
    Brass Contributor
    Mar 14, 2023
    That works perfectly for my need - thanks for your time and help