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

[Solved/Closed]
Report
Posts
2
Registration date
Wednesday March 27, 2013
Status
Member
Last seen
March 28, 2013
-
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
-
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.

15 replies

Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
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.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
2
Registration date
Wednesday March 27, 2013
Status
Member
Last seen
March 28, 2013

hi, Zohaib, thank you for your reply.
I have ten sheet in one file. when I do the special paste , I have to paste them one by one? no other better solution?
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
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?
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
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
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
thank you very much.
Posts
54
Registration date
Tuesday April 2, 2013
Status
Member
Last seen
November 27, 2013
44
@ 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
Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
486
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
thank you ,acutally I use this now. but I still feel it is not good enough and I am looking for the way can be export the whole file with value only.
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
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.
Thank you. I can not download the file since it is blocked.
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
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
Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
486
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
Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
486
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
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
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
THank you all for all your help
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
Hi ziyun,

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