How do I get excel file ( no formula) from another one ( with fo Solved/Closed

Question

Hello, 
I have a big excel file with lots of formula and link. now can I export these data only( no link or formula) to another excel file?  Thank you.

Zohaib R · Answer

Hi ziyun,

The easiest way for me is to copy the entire sheet and then use Microsoft Excel's "Paste Special" feature to paste only the value after that I apply the desired formatting to the pasted data. This way the formulas will not be pasted and links will not appear as hyperlink. To know more about "Paste Special":

http://office.microsoft.com/en-in/excel-help/use-paste-special-to-insert-static-or-linked-data-into-excel-HP001108276.aspx

Do reply with results.

Zohaib R · Answer

Hi ziyun,

Do you want to paste this data in another file with 10 different sheets or all the data should appear in single sheet? Also, are the columns in the source sheets (from where to be pasted) and the target sheet(s) (where data  needs to be pasted) same?

Anonymous User · Answer

hi, Zohaib,
I want to paste the data with  10 different sheets. and yes, the columns in the source sheets and the target sheets are same.  do you have better solution? Thakn you

Zohaib R · Answer

Hi ziyun, 

I am working on this. Once I find a fix I will upload the sample file and provide you the link.  
  
Thanks & Regards 
Zohaib R 
#iworkfordell

Anonymous User · Answer

thank you very  much.

sgmpatnaik · Answer

@ Ziyun 

Hi 

Please Check This Link may be it will give some idea to you, if not please inform us again 

https://answers.yahoo.com/question/index?qid=20101015150144AAKq8Xw 

or

http://www.vbaexpress.com/forum/archive/index.php/t-18185.html


Thanks 

Patnaik

TrowaD · Answer

Hi Ziyun,

Why all this effort for only 10 sheets?

Just copy your workbook.
Click on the square above the 1 of row 1 and to the left of A of column A.
This will select all the cells of the sheet.
Now right-click --> copy.
Right-click at the same spot and select the paste special option.
Choose value only.
Repeat 9 times and you are done.

This will probably take you less then a minute.

Best regards,
Trowa

Zohaib R · Answer

Hi Ziyun,

I tried and made two Workbooks using Microsoft Excel 2010. The first workbook with the name "SourceSheet" has two Worksheets with some data. Each worksheet in "SourceSheet" has a button which will copy the data in the sheet till the last row. Then you can open the other Workbook with the Name "TargetSheet". It has a button which will paste the entire data from the "SourceSheet". Repeat this process for the other sheet in "SourceSheet", the data will be pasted in the "TargetSheet" from the last row, you will not need to navigate to the last row manually and take the pain of selecting the entire data.

You can download the sample files from the below mentioned link:

http://speedy.sh/F8RWe/TargetSheet.zip

This sheet might not be as per your desire because of the data. However, you can modify the sheets and the code according to your need.

Please revert for clarification.

Zohaib R · Answer

Hi ziyun, 

https://authentification.site might not open from all the computers due to restrictions. Check with another computer and try to download the file. It will be very lengthy to explain the steps to create the two macros in the sheets.   
  
Thanks & Regards 
Zohaib R 
#iworkfordell

TrowaD · Answer

Hi Zohaib and Ziyun,

Personally I'm restricted from downloading archives (like .rar, .zip etc.).

Maybe it will benefit Ziyun to upload each workbook individually.

Just a suggestion.

Best regards,
Trowa

TrowaD · Answer

Hi Ziyun,

See if you like this code:
Sub ValuesOnly()
Dim ws As Worksheet
Dim currentbook As Workbook
Dim sName As String

Set currentbook = ActiveWorkbook
Workbooks.Add.SaveAs Filename:="C:\Documents\NewBook.xls"

currentbook.Activate

For Each ws In Worksheets
    ws.Cells.Copy
    sName = ws.Name
    Workbooks("NewBook").Worksheets.Add.Name = sName
    Workbooks("NewBook").Sheets(sName).Range("A1").PasteSpecial Paste:=xlPasteValues
Next ws
End Sub
Just change 3 bold parts to suit your situation and run the code.

Hope you like.

Best regards,
Trowa

Zohaib R · Answer

Greetings Trowa and ziyun, 

Thank you for the tip TrowaD. I have uploaded individual files for you ziyun; please try downloading them from the following links: 

http://speedy.sh/94xj7/SourceSheet.xlsm 

http://speedy.sh/4w5gf/TargetSheet.xlsm 

Do reply with results.  
  
Thanks & Regards 
Zohaib R 
#iworkfordell

Anonymous User · Answer

thank you

Anonymous User · Answer

THank you all for all your help

Zohaib R · Answer

Hi ziyun,

You are welcome. Please feel free to contact us if you have any further queries.

How do I get excel file ( no formula) from another one ( with fo

15 responses

Similar discussions

Subscribe To Our Newsletter!