Copying a Sheet in a Workbook to Another with Destination-Need Values Instead of Formulas

[rsrasc]

Hi all,

I'm using the following macro to copy information from one workbook to another workbook.

Workbooks("050 FY 2019 Expenses-SSE.xlsx").Sheets("Excel Sum-Reforecast-FY 18").Range("A4:A98").Copy Destination:=Workbooks("FY 18-SSE Actuals Plus Reforecast Budget.xlsx").Sheets("050-REVENUE").Range("A4:A98")

The macro is working but the source file "050 FY 2019...." in column A has only formulas which are being copy to the destination file also as formulas.

I want the macro to copy the values only instead of the formulas. I have search a lot of pages but have not been able to find a solution.

As always your help is appreciated.

[jeffreybrown]

When pasting values only, you need to use two lines in the macro...

Sub ValuesOnly()
    Workbooks("050 FY 2019 Expenses-SSE.xlsx").Sheets("Excel Sum-Reforecast-FY 18").Range("A4:A98").Copy
    Workbooks("FY 18-SSE Actuals Plus Reforecast Budget.xlsx").Sheets("050-REVENUE").Range("A4:A98").PasteSpecial xlPasteValues
End Sub

https://www.thespreadsheetguru.com/t...-only-with-vba

[rsrasc]

Originally Posted by jeffreybrown

When pasting values only, you need to use two lines in the macro...

Sub ValuesOnly()
    Workbooks("050 FY 2019 Expenses-SSE.xlsx").Sheets("Excel Sum-Reforecast-FY 18").Range("A4:A98").Copy
    Workbooks("FY 18-SSE Actuals Plus Reforecast Budget.xlsx").Sheets("050-REVENUE").Range("A4:A98").PasteSpecial xlPasteValues
End Sub

https://www.thespreadsheetguru.com/t...-only-with-vba

Hi Jeffrey,

Thank you for the code, and is working great.

Much appreciated.

Cheers!

[jeffreybrown]

Glad it helped and your welcome.

[p45cal]

There is a one liner:

Workbooks("FY 18-SSE Actuals Plus Reforecast Budget.xlsx").Sheets("050-REVENUE").Range("A4:A98").Value = Workbooks("050 FY 2019 Expenses-SSE.xlsx").Sheets("Excel Sum-Reforecast-FY 18").Range("A4:A98").Value