I have a vba script where I have to copy a row of values from one worksheet to another. I need to only copy values and not formulas. I have read that with PasteSpecial this can be done. My challenge is that I need to use PasteSpecial with Sheet.Cells(,) function because my cells for the destination worksheet are being calculated dynamically. Here is the sample code snippet:
For Each header In desWS.Range(desWS.Cells(1, 1), desWS.Cells(1, lcol))
Set foundHeader = srcWS.Rows(2).Find(header, LookIn:=xlValues, LookAt:=xlWhole)
If Not foundHeader Is Nothing Then
srcWS.Range(srcWS.Cells(3, foundHeader.Column), srcWS.Cells(lastRow, foundHeader.Column)).Copy _
desWS.Cells(2, header.Column)
End If
Next header
With the above code, it is copying formulas as well. Can someone help me with the code as to how I can use PasteSpecial in this scenario to only copy and paste values? Thank you.