Copy array of cell values to range in VBA

Question

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

(a) You have never assigned a value to wsName and NewName (but maybe that is just due to having posted something that isn't a MCVE?) (b) Which line gives the error? (c) Code worked for me once I gave wsName and NewName values, and gave different errors to the one you are getting if I hadn't. — YowE3K
– YowE3K, Commented Sep 3, 2017 at 21:51
I agree with @YowE3K everything should work, as long as the NewName and wsName are correct. This line does nothing: Set Frange = Frange.Resize(UBound(Colm), 1). Frange is already UBound(Colm) rows by 1 column. — user6432984
– user6432984, Commented Sep 3, 2017 at 22:12

Tim Williams · Accepted Answer · 2017-09-04 00:00:15Z

1

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

answered Sep 4, 2017 at 0:00

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Copy array of cell values to range in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related