[Solved] Calc: copy data from a spreadsheet to another
- chetan3125
- Posts: 17
- Joined: Sat Dec 17, 2011 4:39 pm
[Solved] Calc: copy data from a spreadsheet to another
Hi guys,
I am completely new for calc and I desperately need the help.
I have two separate calc documents A ,B and C. (Please see the attachments) I want to copy data from A and B to C sheet. all three documents ar placed in same folder. Please tell me some way to copy data from A and B to C. eg. both A and B sheets contain data from A1:H34 and I want to copy data from sheet A (from these cell only) to sheet c on cells A1:H34 and data from sheet B to sheet C on cell A35:H68.
Please tell me about creating macros for this since I have to do this on each day.
It would be of more help if this can be a single click procedure (by creating a button on sheet C to execute macros)
Any help would be much appreciated.
I am completely new for calc and I desperately need the help.
I have two separate calc documents A ,B and C. (Please see the attachments) I want to copy data from A and B to C sheet. all three documents ar placed in same folder. Please tell me some way to copy data from A and B to C. eg. both A and B sheets contain data from A1:H34 and I want to copy data from sheet A (from these cell only) to sheet c on cells A1:H34 and data from sheet B to sheet C on cell A35:H68.
Please tell me about creating macros for this since I have to do this on each day.
It would be of more help if this can be a single click procedure (by creating a button on sheet C to execute macros)
Any help would be much appreciated.
- Attachments
-
- A.ods
- (12.44 KiB) Downloaded 909 times
-
- B.ods
- (12.35 KiB) Downloaded 673 times
-
- C.ods
- This is the common file and I want to copy data on this From sheet A and B
- (8.57 KiB) Downloaded 604 times
Last edited by Hagar Delest on Thu Dec 29, 2011 7:13 pm, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
OpenOffice 3.1 on Windows 7
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Calc copy data from one spreadsheet to another with macr
Welcome to forum, chetan3125!
You asked a very good and very valid question: How can I learn?
Try here, on this site - http://www.pitonyak.org/ - to download a book AndrewMacro.odt.
This book is very large. But for the first time will be enough to read "5.23.2. Copy Spreadsheet Cells Without The Clipboard" and "6.22. Which cells are used in a sheet?". You may also need "Listing 5.14: Load a document from a given URL."
This is quite a bit! And it works! (Believe me! I know what I'm saying. I'm studied in this book)
You asked a very good and very valid question: How can I learn?
Try here, on this site - http://www.pitonyak.org/ - to download a book AndrewMacro.odt.
This book is very large. But for the first time will be enough to read "5.23.2. Copy Spreadsheet Cells Without The Clipboard" and "6.22. Which cells are used in a sheet?". You may also need "Listing 5.14: Load a document from a given URL."
This is quite a bit! And it works! (Believe me! I know what I'm saying. I'm studied in this book)
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
- chetan3125
- Posts: 17
- Joined: Sat Dec 17, 2011 4:39 pm
Re: Calc copy data from one spreadsheet to another with macr
sorry to bother you John,
but I am very new to this and Icant igure out what exactly the book is saying. Can you please elaborate the procedure for me in steps because in book the procedure seems to me too scattered.
Thanks for your reply.
but I am very new to this and Icant igure out what exactly the book is saying. Can you please elaborate the procedure for me in steps because in book the procedure seems to me too scattered.
Thanks for your reply.
OpenOffice 3.1 on Windows 7
- Hagar Delest
- Moderator
- Posts: 32667
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Calc copy data from one spreadsheet to another with macr
You've to learn. So you've to study the basics about selecting a range, then change the active file and paste the clipboard.
There is no easy step by step to be described here.
There is no easy step by step to be described here.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Re: Calc copy data from one spreadsheet to another with macr
I have to do similar things each and every day and I know very well how to write macros. I would never write a macro for such trivial keystroke operations. It is not worth the effort. It works only if the specific macro for the specific task is availlable on the current system. You never learn how to use the software efficiently in similar situations.chetan3125 wrote: Please tell me about creating macros for this since I have to do this on each day.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- chetan3125
- Posts: 17
- Joined: Sat Dec 17, 2011 4:39 pm
Re: Calc copy data from one spreadsheet to another with macr
I am just stuck on step to change active file. I tried to the same thing in excel by clicking on open then new document and then pasting data in new document which gets captured automatically with clicks in excel macros. can Somebody guide me to do this in calc ?
OpenOffice 3.1 on Windows 7
- chetan3125
- Posts: 17
- Joined: Sat Dec 17, 2011 4:39 pm
Re: Calc copy data from one spreadsheet to another with macr
Is there any other alternative to this ? like VBA ?Villeroy wrote:I have to do similar things each and every day and I know very well how to write macros. I would never write a macro for such trivial keystroke operations. It is not worth the effort. It works only if the specific macro for the specific task is availlable on the current system. You never learn how to use the software efficiently in similar situations.chetan3125 wrote: Please tell me about creating macros for this since I have to do this on each day.
OpenOffice 3.1 on Windows 7
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Calc copy data from one spreadsheet to another with macr
I'm not sure that it would work, but you can try.chetan3125 wrote:I am just stuck on step to change active file. I tried to the same thing in excel by clicking on open then new document and then pasting data in new document which gets captured automatically with clicks in excel macros. can Somebody guide me to do this in calc ?
Each new version of OOo better understand the code of VBA.
Try updating your office suite to the latest version and try to run the macro, which you recorded in Excel.
Perhaps, you may not need to learn programming (Sorry, very sorry. I was wrong again)
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: Calc copy data from one spreadsheet to another with macr
Just ask yourself how fast you can copy (Ctrl+C), switch window (Alt+Tab), and paste (Ctrl+V) in any application without having to write a separate program for each and every application you are using. Writing macros is highly inefficient. You will never use anything else but Excel if your spreadsheet experience depends on such VBA bullshit.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- chetan3125
- Posts: 17
- Joined: Sat Dec 17, 2011 4:39 pm
Re: Calc copy data from one spreadsheet to another with macr
I know its very simple.Villeroy wrote:Just ask yourself how fast you can copy (Ctrl+C), switch window (Alt+Tab), and paste (Ctrl+V) in any application without having to write a separate program for each and every application you are using. Writing macros is highly inefficient. You will never use anything else but Excel if your spreadsheet experience depends on such VBA bullshit.
But It becomes a tedious task for me because each day i have to copy data from 20 sheets to one master sheet (every day). and i thought I can do this with help of macros because I have to copy cells of same range (A1:H34) from these 20 sheets.
Hope you understand my problem becoz this consumes significant time of mine at work.
Hope all you experts can give me the solution !
Thanks.
OpenOffice 3.1 on Windows 7
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Calc copy data from one spreadsheet to another with macr
OK. You told half the problem. Do not hesitate to tell the rest. What are you going to do with the combined data?
Please do not be offended. Most of the newcomers on this forum are also trying to help us.
To do this, they do not describe the whole problem, but only a smaller part.
Perhaps worried that the difficult issue we are not going to answer? In fact, we get bored too easy to answer questions.
You have every day 20 new files with the original data. You combine these data into a single list. What's next?
Please do not be offended. Most of the newcomers on this forum are also trying to help us.
To do this, they do not describe the whole problem, but only a smaller part.
Perhaps worried that the difficult issue we are not going to answer? In fact, we get bored too easy to answer questions.
You have every day 20 new files with the original data. You combine these data into a single list. What's next?
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
- chetan3125
- Posts: 17
- Joined: Sat Dec 17, 2011 4:39 pm
Re: Calc copy data from one spreadsheet to another with macr
Thanks for understanding me John.
you said it correctly. I have to copy data from these 20 files ( actually these are records of 20 persons) on one master file and perform different operations on it like additions, averages and percentages collectively for all of them. Since this is a collective task; it becomes easy to do on master spreadsheet and if I have dat of all 20 persons on it.
Please don't hesitate to ask if you need any more info on this.
Happy to see that somebody is really interested to solve my problem.
May be you can give me an advantage at my workplace if this gets solved.
Thanks a lot.
you said it correctly. I have to copy data from these 20 files ( actually these are records of 20 persons) on one master file and perform different operations on it like additions, averages and percentages collectively for all of them. Since this is a collective task; it becomes easy to do on master spreadsheet and if I have dat of all 20 persons on it.
Please don't hesitate to ask if you need any more info on this.
Happy to see that somebody is really interested to solve my problem.
May be you can give me an advantage at my workplace if this gets solved.
Thanks a lot.
OpenOffice 3.1 on Windows 7
- kingfisher
- Volunteer
- Posts: 2123
- Joined: Tue Nov 20, 2007 10:53 am
Re: Calc copy data from one spreadsheet to another with macr
Is there a new master document each day? How is the data arranged in the master document? Is it on 20 sheets? Do you use database ranges or named ranges?
EDIT: If you have one sheet per person in the master file, you could simply use the menu selection Insert > Sheet from file.
EDIT: If you have one sheet per person in the master file, you could simply use the menu selection Insert > Sheet from file.
Apache OpenOffice 4.1.9 on Linux
- chetan3125
- Posts: 17
- Joined: Sat Dec 17, 2011 4:39 pm
Re: Calc copy data from one spreadsheet to another with macr
Yes. There is new master document every day.
Data is arranged on master on shhet 1 only
eg. data from sheet A will come on rows 1 to 34 data from sheet B will be on rows 35 to 68 and so on.
and, I am not using database or named ranges since I am not using master sheet as database. It's just a simple spreadsheet.
and I am just copy pasting the data.
Data is arranged on master on shhet 1 only
eg. data from sheet A will come on rows 1 to 34 data from sheet B will be on rows 35 to 68 and so on.
and, I am not using database or named ranges since I am not using master sheet as database. It's just a simple spreadsheet.
and I am just copy pasting the data.
OpenOffice 3.1 on Windows 7
- Hagar Delest
- Moderator
- Posts: 32667
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Calc copy data from one spreadsheet to another with macr
A macro would be helpful if it can handle the opening of each of the 20 files one by ones and do the copy/paste task.
I move your topic to the macro forum.
Please post your code and check the material pointed out at the beginning to get code snippets.
The macro would have to:
- be launched from the master file
- open the first file
- copy the range
- close the file
- place cursor at beginning of the destination range (master file)
- paste the range
- open 2nd file
- ...
A list has to be provided for the list of the files and the destination range has to be changed at each new file.
I move your topic to the macro forum.
Please post your code and check the material pointed out at the beginning to get code snippets.
The macro would have to:
- be launched from the master file
- open the first file
- copy the range
- close the file
- place cursor at beginning of the destination range (master file)
- paste the range
- open 2nd file
- ...
A list has to be provided for the list of the files and the destination range has to be changed at each new file.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Re: Calc copy data from one spreadsheet to another with macr
Okay, this is post number 16 of this thread, and we still working on the description of the case that should be solved with macros. Some things are clear now: always the same range, every range of a sheet will be copied in the same sheet with the next range below the previous, 20 sheets in 20 files to copy, .... I think the next step would be how to open those 20 files: Do they follow a certain naming convention? Or maybe they are the only files in a working directory? (Except for the final target file with the macro, of course)
But something is telling me that Villeroy might be right at the end of the day. If something is so difficult to describe, than it is not made for macro automation, because there are lots of parameters that you intuitively specify when selecting something manually, but
it will be a difficult task to specify this all as input variables for a macro operation. (I am talking of input in an abstract sense here, not explicit parameters of a macro function/procedure). But still 20 files is quite a lot and the would justify some effort put in programming a macro.
But something is telling me that Villeroy might be right at the end of the day. If something is so difficult to describe, than it is not made for macro automation, because there are lots of parameters that you intuitively specify when selecting something manually, but
it will be a difficult task to specify this all as input variables for a macro operation. (I am talking of input in an abstract sense here, not explicit parameters of a macro function/procedure). But still 20 files is quite a lot and the would justify some effort put in programming a macro.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
- chetan3125
- Posts: 17
- Joined: Sat Dec 17, 2011 4:39 pm
Re: Calc copy data from one spreadsheet to another with macr
I agree. This looks quite tedious task.
But, if somebody can give me the code for just one operation for first file, I can try the rest as Hagar Delest rightly summed up.
The macro would have to:
- be launched from the master file
- open the first file
- copy the range
- close the file
- place cursor at beginning of the destination range (master file)
- paste the range
----------------------------------------------------------------------------------
Man ! I am disappointed a lot with calc. This task looked so simple in excel.
Here it seems to be a herculean task.
Alas !
But, if somebody can give me the code for just one operation for first file, I can try the rest as Hagar Delest rightly summed up.
The macro would have to:
- be launched from the master file
- open the first file
- copy the range
- close the file
- place cursor at beginning of the destination range (master file)
- paste the range
----------------------------------------------------------------------------------
Man ! I am disappointed a lot with calc. This task looked so simple in excel.
Here it seems to be a herculean task.
Alas !
OpenOffice 3.1 on Windows 7
Re: Calc copy data from one spreadsheet to another with macr
The quick answer then is "stick to Excel". Macro writing is _not_ a trivial task, as you are finding.chetan3125 wrote: Man ! I am disappointed a lot with calc. This task looked so simple in excel.
Here it seems to be a herculean task.
Alas !
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
- chetan3125
- Posts: 17
- Joined: Sat Dec 17, 2011 4:39 pm
Re: Calc copy data from one spreadsheet to another with macr
i dont have excel in office. tht's why this is all.
If you think this task is so simple; Why nobody could help me on this ?
If you think this task is so simple; Why nobody could help me on this ?
OpenOffice 3.1 on Windows 7
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Calc copy data from one spreadsheet to another with macr
Yes, of course. But HOW? By name, which is always the same? First found in the specified directory? Selected in a separate dialog?chetan3125 wrote:- open the first file
I would suggest a slightly different sequence of actions:chetan3125 wrote: - copy the range
- close the file
- place cursor at beginning of the destination range (master file)
- paste the range
- find last used cell at master file (addr1)
- find last used cell at input file (addr2)
- copy data from range A1:addr2 to A(RowAddr1+1):(size A1:addr2) without clipboard (do you remember "5.23.2. Copy Spreadsheet Cells Without The Clipboard"?)
- close the file
My friend! You're absolutely right! The problem is quite simple. But the weekend is so difficult to bring myself to write those 15 lines of code ... Do not wait until tomorrow?chetan3125 wrote:Man ! I am disappointed a lot with calc. This task looked so simple in excel.
Here it seems to be a herculean task.
Alas !
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Calc copy data from one spreadsheet to another with macr
Once more: Because Sundaychetan3125 wrote:Why nobody could help me on this ?
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
- chetan3125
- Posts: 17
- Joined: Sat Dec 17, 2011 4:39 pm
Re: Calc copy data from one spreadsheet to another with macr
Oh, Thanks John.
I can surely wait. No problem at all.
Enjoy your weekend.
I can surely wait. No problem at all.
Enjoy your weekend.
OpenOffice 3.1 on Windows 7
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Calc copy data from one spreadsheet to another with macr
Thanks, chetan3125!
But all the same - to take all the files from the directory?
The order does not matter?
But all the same - to take all the files from the directory?
The order does not matter?
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
- chetan3125
- Posts: 17
- Joined: Sat Dec 17, 2011 4:39 pm
Re: Calc copy data from one spreadsheet to another with macr
No. order doesn't matter.
OpenOffice 3.1 on Windows 7
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Calc copy data from one spreadsheet to another with macr
Thank you waited...
This version is only BETA(!), it has few errors and omissions
But it's a worked. (I hope )
Merry Christmas and Happy New Year!
This version is only BETA(!), it has few errors and omissions
But it's a worked. (I hope )
Merry Christmas and Happy New Year!
- Attachments
-
- CollectData.ods
- Beta-version
- (25.68 KiB) Downloaded 1597 times
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: Calc copy data from one spreadsheet to another with macr
Hi,
i've maybe a simple solution, one macro. Place C.ods in the same folder as A.ods and B.ods, press the button.
Greats R
edits in blue
i've maybe a simple solution, one macro. Place C.ods in the same folder as A.ods and B.ods, press the button.
Greats R
edits in blue
- Attachments
-
- C.ods
- (10.85 KiB) Downloaded 2114 times
- MMove 1.0.6
- Extension for easy, exact positioning of shapes, pictures, controls, frames ...
- my current system
- Windows 10 AOO, LOLinux Mint AOO, LO
- chetan3125
- Posts: 17
- Joined: Sat Dec 17, 2011 4:39 pm
Re: Calc copy data from one spreadsheet to another with macr
Hi John,JohnSUN-Pensioner wrote:Thank you waited...
This version is only BETA(!), it has few errors and omissions
But it's a worked. (I hope )
Merry Christmas and Happy New Year!
This is great!
My sincere thanks & gratitude for this.
I especially apreciate your valuable time and knowledge spent in writing this complex macro and being there till the end; when it seemed impossible.
Life and technology becomes easier due to people like you.
Happy new year
Greetings From India!
Last edited by chetan3125 on Thu Dec 29, 2011 7:04 pm, edited 1 time in total.
OpenOffice 3.1 on Windows 7
- chetan3125
- Posts: 17
- Joined: Sat Dec 17, 2011 4:39 pm
Re: Calc copy data from one spreadsheet to another with macr
This was the simplest solution I could get.F3K Total wrote:Hi,
i've maybe a simple solution, one macro. Place C.ods in the same folder as A.ods and B.ods, press the button.
Greats R
edits in blue
Using this I wrote macros for other 20 files.
Many thanks for your time and guidance.
Please keep it up.
Thanks a ton.
Happy new year
OpenOffice 3.1 on Windows 7
- chetan3125
- Posts: 17
- Joined: Sat Dec 17, 2011 4:39 pm
Re: [Solved] Calc: copy data from a spreadsheet to another
Hi ,
Though this thread is marked as solved , if anybody has a better or simple solution to this;
It's most welcome !
Thanks.
Though this thread is marked as solved , if anybody has a better or simple solution to this;
It's most welcome !
Thanks.
OpenOffice 3.1 on Windows 7
Re: [Solved] Calc: copy data from a spreadsheet to another
Hello Guys bother you but I need some assist on same
Simple question : How can I open 2 different sheets (from same place) using Macro ?
Easy : Run macro -> Open file1 in sheet1-> open file 2 in sheet 2 ->
Thats It and Next step I will do my self, Help would be appreciate
Thnks
Simple question : How can I open 2 different sheets (from same place) using Macro ?
Easy : Run macro -> Open file1 in sheet1-> open file 2 in sheet 2 ->
Thats It and Next step I will do my self, Help would be appreciate
Thnks
Libre Office 5.0/ Open Office /MS Excel/ Windows 7-8