VBA - Copying worksheet from one workbook to another (values, not formulas)

Question

Currently, I am copying the values and formats of a worksheet to a worksheet in another workbook as such:

workbooks("book1.xlsm").sheets(1).range(someRange).copy
with workbooks("book2.xlsm").sheets(1).range("A1")
    .pasteSpecial xlPasteValues
    .pasteSpecial xlPasteFormats
end with

I would like to use something like:

workbooks("book1.xlsm").sheet(1).copy after:=workbooks("book2.xlsm").sheet(1)

The problem is that some of the cells in sheet(1) of book1.xlsm have formulas. Using the second method pulls the formulas, and the resulting values are linked to the data in book2.xlsm

How can I use the second method, but have the values, not formulas, copied?

If not possible, what are some alternatives, aside from the first method, which I am currently using?

What is wrong with the first method? It seems perfectly fine and has always worked for me. — ChipsLetten, Aug 06 '15 at 17:55
Agree with @ChipsLetten; do you simply want something on 1 line? — Grade 'Eh' Bacon, Aug 06 '15 at 18:17
Nothing is wrong with the first method. I was just wondering there was a way to make the second method suit my needs, or there were any alternatives that I was overlooking. — user3089987, Aug 06 '15 at 20:06

paul bica · Accepted Answer · 2015-08-07T14:39:00.183

0

You can break the links after the copy operation:

Option Explicit

Sub copySheetValues()

    With Workbooks("Book2")

        Workbooks("Book1").Worksheets(1).Copy After:=.Worksheets(1)

        Application.Wait Now + TimeValue("0:00:01") 'built-in replacement for "Sleep"

        .BreakLink Name:=Workbooks("Book1.xlsm").FullName, Type:=xlLinkTypeExcelLinks

    End With

End Sub

Note: both files need to be open in the same instance of the Excel application

edited Aug 07 '15 at 14:39

answered Aug 06 '15 at 19:03

paul bica

10,557
4
23
42

I'm getting an error: " Run-time error '1004: Method 'BreakLink' of object '_Workbook' failed" Thank you for your response, though – user3089987 Aug 06 '15 at 19:22
I fixed it by adding a delay of 1 second before breaking the links – paul bica Aug 06 '15 at 20:49
1

Thanks for the update. I got your code to work by changing `Name:=.Name` to `Name:=Workbooks("Book1.xlsm").FullName` – user3089987 Aug 07 '15 at 14:37
thanks for the feedback - I updated the answer accordingly – paul bica Aug 07 '15 at 14:39

VBA - Copying worksheet from one workbook to another (values, not formulas)

1 Answers1

Linked