Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-19-2018, 03:11 AM
rsrasc rsrasc is offline Copying a Sheet in a Workbook to Another with Destination-Need Values Instead of Formulas Windows 10 Copying a Sheet in a Workbook to Another with Destination-Need Values Instead of Formulas Office 2013
Competent Performer
Copying a Sheet in a Workbook to Another with Destination-Need Values Instead of Formulas
 
Join Date: Mar 2014
Location: Germany
Posts: 148
rsrasc is on a distinguished road
Default Copying a Sheet in a Workbook to Another with Destination-Need Values Instead of Formulas

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.
Reply With Quote
  #2  
Old 08-19-2018, 06:55 AM
jeffreybrown jeffreybrown is offline Copying a Sheet in a Workbook to Another with Destination-Need Values Instead of Formulas Windows Vista Copying a Sheet in a Workbook to Another with Destination-Need Values Instead of Formulas Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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
Reply With Quote
  #3  
Old 08-19-2018, 07:50 AM
rsrasc rsrasc is offline Copying a Sheet in a Workbook to Another with Destination-Need Values Instead of Formulas Windows 10 Copying a Sheet in a Workbook to Another with Destination-Need Values Instead of Formulas Office 2013
Competent Performer
Copying a Sheet in a Workbook to Another with Destination-Need Values Instead of Formulas
 
Join Date: Mar 2014
Location: Germany
Posts: 148
rsrasc is on a distinguished road
Default

Quote:
Originally Posted by jeffreybrown View Post
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!
Reply With Quote
  #4  
Old 08-19-2018, 11:11 AM
jeffreybrown jeffreybrown is offline Copying a Sheet in a Workbook to Another with Destination-Need Values Instead of Formulas Windows Vista Copying a Sheet in a Workbook to Another with Destination-Need Values Instead of Formulas Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Glad it helped and your welcome.
Reply With Quote
  #5  
Old 08-19-2018, 04:00 PM
p45cal's Avatar
p45cal p45cal is offline Copying a Sheet in a Workbook to Another with Destination-Need Values Instead of Formulas Windows 10 Copying a Sheet in a Workbook to Another with Destination-Need Values Instead of Formulas Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:00 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft