Forum Discussion

ITTom365

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 MyDestinationTable.ListRows.Add.Range

however this will include formating/formulas

can anyone enlighten me as to how I can can paste only values using listrows method?

Thanks in advance

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

Forum Discussion

ListRows.Add.Range (Values only)

Share

Resources