[Solved] Macro to copy data to active cell from cell above

[Hagar Delest]

Good afternoon (from UK). While entering text, date and numbers data ror-by-row on spreadsheet, it will be helpful to be able to copy into active/cursor cell, contents of cell immediately to cursor-cell. Tips and ideas will be welcome. I use AOO Calc version 4.1.3 on Windows 10 laptop.

[UnklDonald418]

The easiest way would be to record a macro using copy and paste.

[RoryOF]

Would pull down insertion not work as effectively?

[Villeroy]

Code: Select all   Expand viewCollapse view

Sub pullDownData()
ac = getActiveCell(ThisComponent.getCurrentController())
tc = getOffsetRange(ac, -1, 0, 0, 0)
if isObject(tc) then ac.setDataArray(tc.getDataArray())
End Sub

Function getOffsetRange(oRg, nRowOffset&, nColOffset&, nRowResize&, nColResize&)
Dim addr
'calls: getRangeByAddress
   addr = oRg.getRangeAddress()
   addr.StartRow = addr.StartRow + nRowOffset
   addr.EndRow = addr.EndRow + nRowOffset
   addr.StartColumn = addr.StartColumn + nColOffset
   addr.EndColumn = addr.EndColumn + nColOffset
   if nRowResize > 0 then addr.EndRow = addr.StartRow + nRowResize -1
   if nColResize > 0 then addr.EndColumn = addr.StartColumn + nColResize -1
   getOffsetRange = getRangeByAddress(oRg.getSpreadsheet(), addr)
End Function


'pass a spreadsheet-document, sheet or range  together with a c.s.s.table.CellRangeAddress
'return empty if oAddr out of bounds or wrong obj
Function getRangeByAddress(obj, oAddr as com.sun.star.table.CellRangeAddress)
on error goto nullErr:
Dim oSheet
   If obj.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
      REM use the sheet specified by given address
      oSheet = obj.getSheets.getByIndex(oAddr.Sheet)
   else
      REM use given object (range/sheet) as parent range
      oSheet = obj
   endif
   getRangeByAddress = oSheet.getCellRangeByPosition(oAddr.StartColumn,oAddr.StartRow,oAddr.EndColumn,oAddr.EndRow)
exit function
nullErr:
   getRangeByAddress = Null
End Function

Function getActiveCell(oView)
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
   as1()  = Split(oView.ViewData, ";")
   lSheet = CLng(as1(1))
   sDum = as1(lSheet +3)
   as1() = Split(sDum, "/")
   on error goto errSlash
      lCol = CLng(as1(0))
      lRow = CLng(as1(1))
   on error goto 0
   getActiveCell = oView.Model.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
exit Function
errSlash:
   if NOT(bErr) then
      bErr = True
      as1() = Split(sDum, "+")
      resume
   endif
End Function


Edit: forgot to paste function getRangeByAddress

[hubertT]

Thank you UnklDonald418 and Villeroy; and RoryOF whose point looks to be exemplified by Villeroy's macro. I will try the options then report-back.

[RoryOF]

My point is even simpler than Villeroy's macro: if the intention is to copy the previous data, then edit in the changes, it is simple to select the previous cell and either copy/paste or pull it down to the next cell, then edit the changes.

[Villeroy]

Preserving any formula:

Code: Select all   Expand viewCollapse view

Sub PullDownContent()
ac = getActiveCell(ThisComponent.getCurrentController())
rg = getOffsetRange(ac, -1, 0, 2, 1)
if isObject(rg) then rg.fillSeries(com.sun.star.sheet.FillDirection.TO_BOTTOM,com.sun.star.sheet.FillMode.SIMPLE,0,0,0)
End Sub

with the same helper functions as above

[hubertT]

Further thanks, RoryOF and Villeroy.

Recorded macro worked, UnklDonald418, but ended in edit mode or with cell highlighted. Villeroy's corrected macro appears to do 'cleanest' and best. UnklDonald418's and Villeroy's pullDown options are ruled out as they pulled-down formatting.

Here's the Villeroy macro which I will use:

Code: Select all   Expand viewCollapse view

Sub pullDownData()
ac = getActiveCell(ThisComponent.getCurrentController())
tc = getOffsetRange(ac, -1, 0, 0, 0)
if isObject(tc) then ac.setDataArray(tc.getDataArray())
End Sub

Function getOffsetRange(oRg, nRowOffset&, nColOffset&, nRowResize&, nColResize&)
Dim addr
'calls: getRangeByAddress
   addr = oRg.getRangeAddress()
   addr.StartRow = addr.StartRow + nRowOffset
   addr.EndRow = addr.EndRow + nRowOffset
   addr.StartColumn = addr.StartColumn + nColOffset
   addr.EndColumn = addr.EndColumn + nColOffset
   if nRowResize > 0 then addr.EndRow = addr.StartRow + nRowResize -1
   if nColResize > 0 then addr.EndColumn = addr.StartColumn + nColResize -1
   getOffsetRange = getRangeByAddress(oRg.getSpreadsheet(), addr)
End Function


'pass a spreadsheet-document, sheet or range  together with a c.s.s.table.CellRangeAddress
'return empty if oAddr out of bounds or wrong obj
Function getRangeByAddress(obj, oAddr as com.sun.star.table.CellRangeAddress)
on error goto nullErr:
Dim oSheet
   If obj.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
      REM use the sheet specified by given address
      oSheet = obj.getSheets.getByIndex(oAddr.Sheet)
   else
      REM use given object (range/sheet) as parent range
      oSheet = obj
   endif
   getRangeByAddress = oSheet.getCellRangeByPosition(oAddr.StartColumn,oAddr.StartRow,oAddr.EndColumn,oAddr.EndRow)
exit function
nullErr:
   getRangeByAddress = Null
End Function

Function getActiveCell(oView)
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
   as1()  = Split(oView.ViewData, ";")
   lSheet = CLng(as1(1))
   sDum = as1(lSheet +3)
   as1() = Split(sDum, "/")
   on error goto errSlash
      lCol = CLng(as1(0))
      lRow = CLng(as1(1))
   on error goto 0
   getActiveCell = oView.Model.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
exit Function
errSlash:
   if NOT(bErr) then
      bErr = True
      as1() = Split(sDum, "+")
      resume
   endif
End Function

Thank you again. I will try to setup a 'function key combination' to run it.

Please consider the following further points and say if I need close this topic s 'solved' and start new topics to address them, or, if the additional points can be dealt with here under this post:

Further points for the macro:
(1) Run the macro as it is, here.
(2) Run and directly into cell-edit mode.
(3) Run and move directly to next cell to right.
(4) Run and then move back-to-left (a number of columns -presently from column W back to column J), and down one row, ready to start next row's data entries

[Villeroy]

I keep the generic helper functions in a global library named "Calc" since they are useful with many other macros.
getActiveCell is extremely useful because there is always one active cell even if the user selected a range, multiple ranges or some kind of shape. As long as ThisComponent points to a spreadsheet document, I never got any error from this function call. There is no built-in API method to access this object directly. In Excel-VBA the active cell is one of the most prominent objects.
getOffsetRange works like spreadsheet function OFFSET or VBA oRange.Offset(x,y).Resize(m,n). I used to use it in dozends of macros.
getRangeByAddress(obj, addr) is not particularly useful but more handy than obj.getCellRangeByPosition(addr.StartColumn, addr.StartRow,addr.EndColumn,addr.EndRow)

Code: Select all   Expand viewCollapse view

Sub SomeMacro()

GlobalScope.BasicLibraries.loadLibrary("Calc")

ac = getActiveCell(ThisComponent.getCurrentController())
tc = getOffsetRange(ac, -1, 0, 0, 0)

End Sub

If the calling macro and the "Calc" library are embedded in a document, leave out "GlobalScope.".

[hubertT]

I note the further 'SomeMacro()' routine, Villeroy, but unfortunately, I do don't have sufficient understanding the lead you intend it to give me; but I will read & reread it in conjunction with your copy-macro that solved the originating topic I posted.

In the meantime, 'danke sehr'.