Hi guys,
I want to make macro that will allow user to copy data by value (results of the formulas (values) and not formulas it self) from one workbook to another.
Source workbook should remain closed when coping data from it into the new, opened excel workbook.
Can you help me get the code for this?
P.S. Source workgroup contains more the one worksheet so I need to be able to choose from what worksheet Im coping the data by name for example.
Thank you in advance.
Last edited by Dolke; 01-15-2018 at 04:18 AM.
What kind of help are you looking for?
Im looking for a help to make a marco that does this.
I have found a lot of similar solutions for example this:
So basically can you help me modify/fix this code to get the functionality that Im describing?
Please Login or Register to view this content.
You need to supply details.
Im not sure what other details would you need but ok let me explain once more.
I have one workbook (I accidentally called it workgroup in previous posts) and that excel workbook contains several sheets. Each sheet is full of formulas.
Lets call this workbook source.xlsx
Then Im working on something else and for that I open new blank workbook (lets name it destination.xlsx) and I want to copy entire sheet (one of them) from source.xlsx to destination.xlsx without opening source.xlsx.
Source xlsx is actually file located somewhere on our local area network and its usually located on another network PC
Also when copying data from source to destination I only need values. So basically I need results of the formulas in the sheet that Im coping and not copy of the formulas it self.
I hope this clears what Im trying to get.
If you need more info please let me know.
You can use the macro recorder to do this. Once you get the code you can post back to get the code more efficient.
The workbooks have to be opened in order to copy and paste.
When you are in the macro recorder.
Start from the source workbook.
Open the destination workbook.
Copy from the source workbook, pastespecial in the destination workbook, save and close the destination workbook
Post back with the code.
Im not sure how can I record macro and copy entire spreadsheet as a value and not formulas and also how can I copy the content with macro recorder and not to open source file?
Also
I believe this is not true. You can copy content without opening a file.The workbooks have to be opened in order to copy and paste.
There are some soruces here:
https://stackoverflow.com/questions/...aster-workbook
Last edited by Dolke; 01-12-2018 at 08:00 AM.
Oh yes you can but what is the advantage?You can copy content without opening a file.
Well anyhow John Walkenbach describes one way to read data from closed files, see link.
http://spreadsheetpage.com/index.php...a_closed_file/
Alf
Also you can use PowerQuery add-in for Ex2010 Pro Plus where you can select from closed workbook what you want to "import" and show in destination file.
Advantage is the folowing:Originally Posted by Alf
I have file (source file) somewhere on one PC in the LAN.
There there is a spreadsheet with lot of formulas. I dont want to navigate all the time and open the file and copy/paste the results of that formulas into the new working workbook.
Instrad I can click one button and get those results over the LAN instantly and not copy the formulas but values only.
Adjust sheet name & column header.
Please Login or Register to view this content.
Thank you jindon for the code.
Can you please clarify why do I need column header?
I want to copy entire sheet not just one column.
Also Im not sure what do I need to put for column header...
Last edited by Dolke; 01-12-2018 at 09:38 AM.
Isn't your code just importing col.B?
If you want all
Please Login or Register to view this content.
No, not only column B,
I want to import everything from specific spreadsheet...
So if source file has 5 sheets I want to be able to select specific sheet (by name) and get all data by value from that sheet.
Let me check the new code...
Yes, it is.
Please Login or Register to view this content.
Sir,
It seems that this code is working.
Im testing it now and I will let you know if all is good.
Thank you and I will need a donate for a beer, coffee or what ever you drink.
I might need some small extension but as I said testing it now and I will let you know
It seems that its working...
Once small thing though.
Is there a way to set this macro to run automatically when file containing it opens so it can grab fresh data from that source file each time?
Thanks
Paste the code on to ThisWorkbook code module
If you change the procedure name from"test" to something else, make sure you also change the bold part.Please Login or Register to view this content.
Im getting this:
http://prntscr.com/hzhm85
Can you upload a workbook that you get error?
Its just blank workbook with macro...
As source Im using one excel file that I have on one PC in the LAN.
I don't get error, so I'm asking.
Let me try one more time to see whats going on.
Thank you.
Dolke
If you are getting such error, most probably "test" procedure is placed in sheet code module.
If so, 2 options.
1) Move "test" procedure to a standard module.
While you are in VBE, [Insert] - [Module] and move the code onto that module.
The delete the code in sheet code module.
2) Change ThisWorkbook code to
Where "Sheet1" is the name of the sheet in which the code is placed.Please Login or Register to view this content.
Hi guys,
Thank you all for taking interest in this topic.
Special thanks goes to Jindon for helping with the code. Code works like a charm!
Sir, I would like to make a small donation and buy you a beer so if you have paypal please let me know via PM.
I will mark this thread as SOLVED.
Cheers
Dean
OK, that's good.
Your feedback and "Thanks" made me happy already. Thanks anyway.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks