Add new line with date, copy & paste and then repeat again.

[mickel]

Total newbie to Libreoffice Calc after 35+ years of Excel and I need help please to create a macro in Calc.
In the attached spreadsheet example, I want to click on the green cell and run the macro.

When the macro runs it inserts a new line, enters today’s date in cell B7, copies F6 to F7, then moves the cursor to C7 a which point I manually insert the transaction and debit or credit cells.

Then the macro can be run again to create the next new line below the last input. It is at this point I have failed after many hours of trying to resolve the issue.
I recorded the actions but it will only work once and I don’t know how to change the values in the macro to get the new line to insert below the last input.

test spreadsheet.ods (10.7 KB)

[EarnestAl]

I cannot help you with a macro but then I am uncertain as to why you need one:

1. In cell B7 press Ctrl+; to enter today’s date. Press Tab
2. Enter the rem code for the item. Press Tab
3. Enter debit amount or Tab to credit column and enter amount
4. Click in next empty cell in column B or down-arrow and then left-arrow two or three times.

A small modification to the formula in Column F will display blank cells until a value is entered in D or E. I dragged it down until row 100

test spreadsheet99668EA.ods (15.6 KB)

[Zizi64]

The Macro recorder works in the Calc and in the Writer applications (But not in Draw and Impress.)
.
You must use the “regular” ways during the macro recording: the choosing of the Menu items will be recorded, but the usage of the most of Hotkeys will not be.
.
it is better (it is more effective) to write your LO macros based on the API functions and procedures. It is basicly different than the MS VBA. The API works in all of the applications.

[mickel]

Thank you for replying @EarnestAl . Perhaps my original attached example was not explicit enough in the regard to needing a macro to perform the action. I have attached a better file where it should be more obvious what I am doing.

I have multiple things going on in the same sheet and needed the same macro to perform in different areas.

Your suggestion was fine and worked well if I had only one “Bank” input per sheet.

Before you had replied I went back to Excel and created a macro in VBA which worked. I opened the Excel (.xlms) file in LO and then saved the file as .ods. I couldn’t believe it, the VBA macro actually works in LO without having to change anything!

The macro is under Tools/Macros/Run Macro/test spreadsheet/VBAProject/Module1

I am more than happy with the result which has now convinced me to go over to Linux and LibreOffice and say goodbye to Microsoft.
test spreadsheet - with VBA macro.ods (33.1 KB)

[mickel]

Thank you @Zizi64 for your informative post. I think I have a lot more learning to do.

[joshua4]

It is always encouraging to see people ready to make LO part of their daily regimen. But do not be tricked. As @EarnestAl has said, practical LO macros usually do depend on the UNO API. In no way should you assume that LO will generally succeed in executing an Excel macro.