CODEWITHSUNDEEP
arraysexcelvbacopyformulas
aforward
aforward

Reputation: 133

Copy array of cell values to range in VBA

I am trying to copy a sheet to a new workbook with only values, not formulas. I need one column to retain formulas.

It may not be the best method, but I am trying to copy the formulas to a variant array, then copy the values, then add the array of formulas to the new worksheet.

I am using the following code but getting an error. "Run Time Error: 1004 Application defined or object defined error.

Set wbNew = Workbooks.Open(NewName)

Dim Colm As Variant
Dim Frange As Range

Colm = wbNew.Sheets(wsName).Range("F1:F100").Formula

wbNew.Sheets(wsName).UsedRange.Value = wbNew.Sheets(wsName).UsedRange.Value

Set Frange = wbNew.Sheets(wsName).Range("F1:F100")

Set Frange = Frange.Resize(UBound(Colm), 1)

Frange.Value = Colm

Upvotes: 1

Views: 1112

Answers (1)

Tim Williams
Tim Williams

Reputation: 166980

This works for me:

Set wbNew = Workbooks.Open(NewName)

Dim Colm As Variant
Dim Frange As Range

Set Frange = wbNew.Sheets(wsName).Range("F1:F100")

Colm = Frange.Formula

With wbNew.Sheets(wsName).UsedRange
    .Value = .Value
End With

Frange.Value = Colm

Upvotes: 1

Related Questions