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

rsrasc · #1 08-19-2018, 03:11 AM

Hi all,

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

Code:

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 · #2 08-19-2018, 06:55 AM

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

Code:

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 · #3 08-19-2018, 07:50 AM

Quote:

Originally Posted by jeffreybrown

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

Code:

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 · #4 08-19-2018, 11:11 AM

Glad it helped and your welcome.

p45cal · #5 08-19-2018, 04:00 PM

There is a one liner:

Code:

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copying formulas	otuatail	Excel	3	06-03-2017 05:20 AM
Copying Every Other Row from one workbook to another.	eleno5485	Excel	1	04-07-2017 03:30 PM
Can a Data Source be one sheet in a multi-sheet Workbook	nfotx	Mail Merge	1	07-01-2015 12:55 AM
large workbook needs restarted every month with deletions and resort without losing formulas	llhail	Excel Programming	1	06-02-2015 05:34 PM
Adding rows and copying formulas	ejtoll	Word Tables	1	12-05-2012 05:09 PM