Copy/paste a range as values in VBA, instead of the entire sheet

user_9437110 0
Viewed 3153

Right now I am doing this:

xlwb.Sheets(curSheet).Cells.Copy
xlwb.Sheets(curSheet).Cells.PasteSpecial (xlValues)

This lets me copy/paste the entire sheet in-place as-values.

However I technically only want to do this for all columns between row 1 and some row near the bottom, call it finalRow. There is some content after that I wish to leave as formulas.

How can I copy/paste the rows from 1 to finalRow as-value? I'm technically running this code from Access VBA but this code is for Excel.

ms-access vba excel
2 Answers

You can use the following syntax

xlwb.Worksheets(curSheet).Rows(1 & ":" & lastRow).Copy
xlwb.Worksheets(curSheet).Range("A1").PasteSpecial xlValues
Application.CutCopyMode = False

Change the paste range as required.

user_62412350

Skip the clipboard when copying only values:

With xlwb.Sheets(curSheet).Rows("1:" & lastrow)
    .Value = .Value
End With
user_48515900
Related