I would like to create a macro to copy a cell range(one row, C56:J56) from my invoice template file: “C:\CalcWork\Invoice.ods” and paste it to the first empty row in column A of my invoice log file: “C:\CalcWork\InvoiceLog.ods”. It would need to be run from a button on sheet2 of “Invoice.ods” and pasting into sheet1 of “InvoiceLog.ods”.

I am doing this invoice for a family member and would like the process to be a simple button click for the usual reasons. Additionally, I realize that a database would probably be a better long term solution.

I know that my problem may seem trivial or dumb or “answered a thousand times” so I'll apologize up front. But I have spent hours trying to find this exact answer. I have read quite a bit of Mr Pitonyak's Useful Macro Information, and numerous posts from a very knowledgeable poster by the name of Villeroy however my implementation, or lack there of, seems to be the problem.

This is all very new to me and I don't know if I'm attacking it from the wrong angle/keyword, or maybe this is much more complicated than it would seem. I find very little on accessing the second file and doing the pasting part. I've tried CopyRange, but maybe it needs to be a GetDataArray SetDataArray type thing. It's a bit overwhelming when you've never tried this stuff before. In any event I'll post what I started working on below. If nothing else it should make for a good laugh.

Maybe there needs to be a thread on what you can't do. But if it can be done, or done in a different way I'd appreciate any solutions.

Why do you try to make an invoicing system if you are not a programmer?Why do you try to use a free-hand calculator to store structured data?First you've got to learn programming. Then you may write something like this.Indeed, this has been done a thousands time and the better solutions are not based on spreadsheets.

If it has to be self-made and if it is supposed to dump invoices into Calc, then it should be based on a database with a tiny little bit of supplementary macro code.

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

Tom, I can't give you a full answer to your problem, but I hope the following may be helpful.

1. You state your problem and give your macro, but do not say what happens when you run the macro. Does it give an error message and if so what? Does it not give an error message, but do nothing at all? Does it not give an error message, but does something you don't want or expect?2. I can't see a definition of oSheet. I was expecting to see a definition of an object oSheetB in terms of oDocB.3. The following link may be useful for copying and pasting, although I don't think it does exactly what you want (viewtopic.php?f=20&t=53098)

There is no reason why the input cells should be copied to the end of the list. Just insert everything on top of the list. Or insert anywhere with a time stamp, so you can generate any sort order. Or use a database table with a database form without a single line of stupid Basic code.

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

Villeroy knows a lot about this software and you would do well to heed his advice.

Perhaps you need a quick and dirty solution until you have time to put together a better system. If I was starting to use this software now I would probably learn Python but the consensus a few years ago seemed to be to use StarBasic.

I've cobbled this together from code I have been using for several years:

Villeroy wrote:Why do you try to make an invoicing system if you are not a programmer?Why do you try to use a free-hand calculator to store structured data?First you've got to learn programming. Then you may write something like this.Indeed, this has been done a thousands time and the better solutions are not based on spreadsheets.

If it has to be self-made and if it is supposed to dump invoices into Calc, then it should be based on a database with a tiny little bit of supplementary macro code.

“Why do you try to make an invoicing system if you are not a programmer?”

Very good question. The short answer is I haven't found a better solution. However with all the questions you answer around here I feel I must do better than that.

You see, I've worked with countless people that use QB(Quickbooks) and they have to change out their software constantly as each version gets “sunset”. And they tend to run into all sorts of issues every time. I'm looking for a more permanent solution, something that doesn’t change often, for multiple reasons. Also I would like it to end up running on the Ubuntu OS and have not found a good replacement for QB. What I'm doing is trying to learn a few things for myself while helping my older cousin, who is more of an uncle in all reality. He was a longtime dairy farmer who became a landscaper as the times have changed. He is one of the most giving, hardest working people I have ever known. However his billing/accounting methods are; well, just beyond post-its. His success is of the utmost importance to me as my mother was born in that farmhouse and I grew up there.

“Why do you try to use a free-hand calculator to store structured data?”

Same short answer as above. Long answer: Keeping in mind that I actually had to explain what “click on it” meant when I first sat him down on a computer, I need something that he will actually use. I would like the Invoice log file for two reasons, one as a back up that he never has to see or even know is there, and two something that I can use to easily total quarterly taxes and revenues. Also this will allow me to easily make some charts and reports. I also foolishly thought that this would be much easier than it has turned out to be(I'm sure you've heard that before . Every time I make a macro to solve a problem it seems to create 2 more. However, I'm very close to a one click macro that will copy the invoice to a new file with the invoice number as a name for reference, then log the key information to the invoice log file, and then return to a clean invoice ready to start all over. This is a good start.

“First you've got to learn programming. Then you may write something like this. Indeed, this has been done a thousands time and the better solutions are not based on spreadsheets.”

No I am not a programer, some times I wish I was. But I never was a webmaster, and that didn't stop me; a sound engineer and that didn't stop me, or even a dad until recently. And while doing this I got to thinking that there are probably a lot of small business out there who can't afford a dedicated secretary, who may also struggle with billing and invoicing. And if I could come up with a system to handle all the appointment setting and do the customer service and billing for 10-20 local small business, I might be able to drop the 9-5 and spend some more time with my son.

I know this is not the right solution for that scenario, but a temp fix for my cousin.

“There is no reason why the input cells should be copied to the end of the list. Just insert everything on top of the list.”

Your probably right about that but I thought it may save me some trouble down the line.

“Or insert anywhere with a time stamp, so you can generate any sort order.”

The invoice number is a timestamp and is included in the range. yymmddhhmmss

“Or use a database table with a database form without a single line of stupid Basic code.”

Now your speaking Greek to me, sorry.

That being said, I would like to ask again. What do you recommend? Learning a programing language? If so which? Should I look toward Base, or MS Access? Neither? Web-based is nice no crashes- Freshbooks, Xero? Should I try to hire someone to build a program? Or would you recommend something completely different? I am very open to suggestion at this point.

Thank you all for your thoughts and time, I am most appreciative to all the help you all have provided.

Wow, Perfect! I wish I could buy you a steak dinner!! I have spent weeks playing around with all sorts of code, to no avail. Then you come along with this and it does it on the first try. Do you know if there is there something i can add so it saves and closes so the user never sees the InvoiceLog file?

I have spent weeks playing around with all sorts of code, to no avail.

Totally inadequate approach towards programming.

Once again your undoubtedly right, however I unfortunatly am more of a hands on learner. Given this reply am i to take it that given my circumstances that you feel spending time learning OOO Basic will benefit me in future en-devours? Or is time better spent elsewhere?

I decided to bring this thread back to life, because I have some remarks and some questions. I consider myself a VB and OO Basic amateur and I have found macros an interesting task to play with and learn, but not much more than that. I have already moved to databases for the real business stuff actually...

One fact I discovered, playing around with macros to copy cell content between sheets or docs, is that there are quite a lot different ways to do the same thing. Which of them should be used is only a matter of perspective or preference, or are there other parameters one should take into account?

After doing some R&D, trying to make a macro to copy NON Adjacent cells into the first available line of another sheet, this was what I managed to think of:

As you can see, the only way I found was to start off from patel's code and do the process over for each separate cell. Trying to use Named Range or Cell Range resulted in pasting the cells following source cell positions, not in a single line, that wasn't what I was aiming for. **The last 4 lines are just for "refreshing" source sheet and get it ready for new input. Could someone please shed some light on these:

1) I am pretty sure my code includes unnecessary lines, due to recurrent processes, but couldn't find any. Whatever I removed resulted in malfunction. Are there actually unnecessary lines?2) Why does patel use Dummy() for his dispatcher and not Args(), as in other similar lines? If answer is too complicated, never mind...3) Using this macro, the pasted cells follow Normal Paste. Trying to use "oDispatcher.executeDispatch(oFrameB, ".uno:PasteSpecial", "", 0, Dummy())" instead, leeds to "Special Paste" dialog box appearing as soon as macro reaches this line. Clicking OK lets it finish normally, but I haven't found out how to automate the pasteSpecial call, so the box doesn't even pop up.4) Since cell F3 is an auto-date cell (=TODAY()), this macro copies the whole cell and not only the date value. Resulting, of course, in a whole row changing values every day.... not very useful! I guess the way to deal with that is the answer to 3), pasting only the value and not the formula. What if one would keep Normal Paste in his dispatcher call and then DELETE formula (+whatever else) in the pasted cells? Could that work also?

A macro does not need to select anything in the document.Avoid dispatches.This macro destroys any spreadsheet functionality (if there is any at all) since it disregards references.Don't use any macros at all before you are really proficient with spreadsheets. Spreadsheets help non-programmers to handle data in more visual ways. Doing the most trivial spreadsheet stuff by means of macro code is counterproductive. Any program written in a script language can do this kind of list keeping stuff by far more efficiently with 5 lines of code without any office suite being installed.

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

Thank you for your answer Villeroy! I really apreciate your deep knowledge and your advice on so many things

Villeroy wrote:... This macro destroys any spreadsheet functionality (if there is any at all) since it disregards references....

I didn't get what you mean there. Spreadsheet functionality isn't all about entering and sorting data and doing calculations with them? How does a macro (aiming at selecting some of these data and arranging them differently) destroy that?

Villeroy wrote:... Any program written in a script language can do this kind of list keeping stuff by far more efficiently with 5 lines of code without any office suite being installed.....

As I already wrote, I don't use this spreadsheet for any productivity purpose, but I would like to be able to propose something to any friend of mine, who would like a rather simple option to do a rather simple accounting routine. If he wants to be really professional, I totally agree with you, there are other good options. But if not? Could you suggest something?

Hello,what do you want to do? As far as I understand the macro, do you want to copy the contents (not the cells themselve) of cell F2, F3 and B7 from Sheet1 to Sheet3 (in first empty row) and afterwards increment F2 by 1. One cell (which one?) should be cleared. Correct?

giorgoskn wrote:Thank you for your answer Villeroy! I really apreciate your deep knowledge and your advice on so many things

Villeroy wrote:... This macro destroys any spreadsheet functionality (if there is any at all) since it disregards references....

I didn't get what you mean there. Spreadsheet functionality isn't all about entering and sorting data and doing calculations with them? How does a macro (aiming at selecting some of these data and arranging them differently) destroy that?

Enter any formulas, named ranges, charts, validation, conditional formatting, pivot tables, whatever to your sheet. Then run the macro which appends some data somehow. All the formulas, names and charts will not include the new data. You will have to adjust all references which may be a lot more work than doing the job of data insertion manually and properly

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

mikele wrote:Hello,what do you want to do? As far as I understand the macro, do you want to copy the contents (not the cells themselve) of cell F2, F3 and B7 from Sheet1 to Sheet3 (in first empty row) and afterwards increment F2 by 1. One cell (which one?) should be cleared. Correct?

Yes mikele, exactly that. CellD that made you wonder is wrong, it's cell B7 (CellC) actually - I just copied the older txt file by mistake.

What I still haven't figured out is how to copy only values and not all cell contents (including formula and formatting). Using Uno:PasteSpecial instead, brings up the Paste Special dialog box, which is OK, but needs 1 additional click for every pasted cell. Is there a way to avoid that?

I suspected there were too many lines of code in my macro for this simple task, but you nailed it man!! Perfect! *Just 1 small detail: For B7 I needed it to copy text, not value, so I will change it a little

@ the casual reader: I've updated my ready-to use macro and wrapped it into an example document. viewtopic.php?f=21&t=93099 (copy data from sheets in same document). And there is also the other variant viewtopic.php?f=21&t=77069 which copies a single sheet from every spreadsheet or csv file in a specific folder into a master file derived from the attached template.

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.Ubuntu 18.04, no OpenOffice, LibreOffice 6.x