Macro to use relative references

[MarkoV]

Hello,

I’m not very familiar to the LO, so I’m asking an advice how this macro can be recorded. Let’s start from the beginning what i’d like to do in a first place:
I want to make an calc file, where are two worksheets - one for data entering(10-15 rows, date work numbers qty’s etc.) which would include pushbutton. Second sheet is for raw data from first sheet - when data is entered to the first sheets cells and the pushbutton is clicked, the data will be exported to the second worksheet row by row(every click makes new row with the data which is in these needed cells). I’ve no idea how to do it, but research from the web gave me one thought(probably there’s easier and simple/better ways also, plase reccomend:) )

I could record a macro, which copies these cells to the next sheet(for example starting from scratch - A1) and link it with an pushbutton - when pushbutton is clicked, the macro would be ran and I get the data to the next sheet. But the problem is, that I don’t know how to make the macro the way, that every next click would send the data to the next available row(A1,A2,A3 etc…). If I make the macro like this, it always copies the data to A1, but how to make it relative??

Best Regards,

Marko.

[mauricio]

If you attach file with exactly you need, I can help you with the code.

[SM_Riga]

Hello @MarkoV

While macro recorder is of limited functionality, it still can be used for capturing some basic actions though. Basically, it records the sequence of actions taken, so if you are able to create common repeatable pattern/algorithm for these actions and you are not familiar with coding, macro recorder could be a solution. In your case, the action set for recorder could be:

1. Select row/rows to be copied
2. Copy them
3. Click on the second sheet
4. Click on the first row with data
5. Press CTRL + Arrow Downto go to the end of data
6. Press Arrow Downonce to move cursor on the first empty line
7. Paste rows

While recording such macro is possible, the length of the code is way over normal and it seems to be a bug on Windows systems, when uno:copy command is executed via push button. See here and here.

The correct approach is to take some time to learn basics of coding and write a few lines for such a task.

I have attached this sample file with both recorded and coded subroutines, so you can study them a bit. Once again, please note recorded macro will not execute copy command when ran via push button.

[MarkoV]

Hello,

Sample file is found on these download links at the end

I want to add needed data to the first sheets unfilled cells. After the data is entered, pushbutton is pressed. This exports the data to the Sheet 2, and the entered data of Sheet 1 will be cleared for new insertion, BUT with one exception(if possible) - the DATE data should not be cleared on first sheet after button press, because there are entered maybe 10-15 units of data with the same date at once, so this saves some time.
Ideal solution would be, that the Sheet2(where data is exported) would be locked for editing with an password(0123456789) for example. This excludes the situation, where someone accidentaly goes to the page and deletes it.

Dropbox - File Deleted

Marko

[mauricio]

Sample 2.ods

Try with:

Sub Save_And_Clean()

	doc = ThisComponent
	sheet = doc.getCurrentController().getActiveSheet()
	target = doc.Sheets.getByName("Sheet2")
	range = sheet.getCellRangeByName("A2:L2") 

	cell = next_cell(target.getCellRangeByName("A1"))
	data = range.DataArray
	copy_to(cell, data)
	
	range.clearContents(23)
End Sub


Function next_cell(cell)
	cursor = cell.SpreadSheet.createCursorByRange(cell)
	cursor.gotoEnd()
	row = cursor.RangeAddress.EndRow
	next_cell = cell.SpreadSheet.getCellByPosition(0, row + 1)
End Function


Function copy_to(cell, data)
	row = cell.RangeAddress.StartRow
	s = cell.SpreadSheet
	range = s.getCellRangeByPosition(0, row, 11, row)
	range.DataArray = data
End Function

[mauricio]

EasyMacro is my extension for more easy develop macro, the same code with this is:

Sub Using_EasyMacro()
	app = createUnoService("net.elmau.zaz.EasyMacro")
	doc = ThisComponent
	range = app.get_range(doc, "A2:L2") 
	data = range.DataArray
	
	sheet = app.get_sheet(doc, "Sheet2")
    cell = app.get_range(sheet, "A1")
    cell = app.get_next_cell(cell)
	app.set_data(cell, data, False)
	
	range.clearContents(23)
End Sub

EasyMacro is in develop, please, report in site project any error.

[mauricio]

But, it’s possible with Pyton too.

doc = app.get_document()
cell = doc['Sheet2']['A1'].next_cell
with doc['Sheet1'] as s:
    s['A2:L2'].copy_to(cell)
    s['A2:L2'].clear()