Pasting range vba in values without using clipboard-CodePudding

I am trying to copy and paste a range in values without using the clipboard, below code works but doesn't copy in values (includes forumlas etc):

any ideas as how to make this work?

NbRowsPnLD1 = PnLD1WS.Range("A1").End(xlDown).Row
PnLD1WS.Range(PnLD1WS.Cells(1, 1), PnLD1WS.Cells(NbRowsPnLD1, 228)).Copy(PnLD2WS.Cells(1, 1)).PasteSpecial xlPasteValues

CodePudding user response：

Something like

With PnLD1WS.Range(PnLD1WS.Cells(1, 1), PnLD1WS.Cells(NbRowsPnLD1, 228))
    PnLD2WS.Cells(1, 1).Resize(.Rows.Count,.Columns.Count).Value2 = .Value2
End With

CodePudding user response：

Copy the Values of a Range by Assignment

Option Explicit

Sub CopyByAssignment()
    
    ' It is assumed that 'PnLD1WS' and 'PnLD2WS' are the code names
    ' of two worksheets in the workbook containing this code.
    
    ' Calculate the last row,
    ' the row with the last non-empty cell in the column.
    ' Most of the time you want to use '.End(xlUp)' instead:
    Dim slRow As Long
    slRow = PnLD1WS.Cells(PnLD1WS.Rows.Count, "A").End(xlUp).Row
    ' ... because if you have empty cells in the column, it will reference
    ' the whole range regardlessly.
    ' The following will 'stop' at the first empty cell and may not reference
    ' the whole desired column range.
    'slRow = PnLD1WS.Range("A1").End(xlDown).Row ' not recommended
    
    ' Reference the source range (the range to be copied from).
    Dim srg As Range
    Set srg = PnLD1WS.Range("A1", PnLD1WS.Cells(slRow, "HT"))
    
    ' Reference the destination range (the range to be written (pasted) to).
    ' Use 'Resize' to make it the same size as the source range.
    Dim drg As Range
    Set drg = PnLD2WS.Range("A1").Resize(srg.Rows.Count, srg.Columns.Count)
    
    ' Copy by assignment.
    drg.Value = srg.Value

End Sub