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

Solved/Closed
Anonymous User - Mar 27, 2013 at 11:43 AM
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 - Apr 12, 2013 at 10:31 PM
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.
Related:

15 responses

Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Mar 27, 2013 at 03:45 PM
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
Anonymous User
Mar 28, 2013 at 09:48 AM
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?
0
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Mar 28, 2013 at 08:30 PM
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?
0
Anonymous User
Apr 1, 2013 at 09:47 AM
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
0
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Apr 3, 2013 at 04:24 AM
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
0

Didn't find the answer you are looking for?

Ask a question
Anonymous User
Apr 3, 2013 at 09:45 AM
thank you very much.
0
sgmpatnaik Posts 52 Registration date Tuesday April 2, 2013 Status Member Last seen November 27, 2013 45
Apr 4, 2013 at 08:24 AM
@ 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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 4, 2013 at 10:54 AM
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
0
Anonymous User
Apr 10, 2013 at 10:15 AM
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.
0
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Apr 6, 2013 at 01:44 AM
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.
0
Anonymous User
Apr 10, 2013 at 10:17 AM
Thank you. I can not download the file since it is blocked.
0
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Apr 11, 2013 at 12:26 AM
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 11, 2013 at 10:43 AM
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 11, 2013 at 11:30 AM
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
0
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Apr 11, 2013 at 11:04 PM
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
0
Anonymous User
Apr 12, 2013 at 08:59 AM
thank you
0
Anonymous User
Apr 12, 2013 at 09:00 AM
THank you all for all your help
0
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Apr 12, 2013 at 10:31 PM
Hi ziyun,

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