[Solved] Macro to copy data to active cell from cell above
[Solved] Macro to copy data to active cell from cell above
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.
Last edited by Hagar Delest on Fri Apr 07, 2017 9:29 pm, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
OpenOffice 4.1.3 on Windows 10
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Macro to copy data to active cell from cell above
The easiest way would be to record a macro using copy and paste.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Macro to copy data to active cell from cell above
Would pull down insertion not work as effectively?
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Macro to copy data to active cell from cell above
Code: Select all
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 |
Last edited by Villeroy on Fri Apr 07, 2017 6:22 pm, edited 1 time in total.
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
Re: Macro to copy data to active cell from cell above
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.
OpenOffice 4.1.3 on Windows 10
Re: Macro to copy data to active cell from cell above
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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Macro to copy data to active cell from cell above
Preserving any formula:
with the same helper functions as above
Code: Select all
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
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
Re: Macro to copy data to active cell from cell above
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:
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
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
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
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
OpenOffice 4.1.3 on Windows 10
Re: Macro to copy data to active cell from cell above
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)
If the calling macro and the "Calc" library are embedded in a document, leave out "GlobalScope.".
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
Sub SomeMacro()
GlobalScope.BasicLibraries.loadLibrary("Calc")
ac = getActiveCell(ThisComponent.getCurrentController())
tc = getOffsetRange(ac, -1, 0, 0, 0)
End Sub
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
Re: [Solved] Macro to copy data to active cell from cell abo
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'.
In the meantime, 'danke sehr'.
OpenOffice 4.1.3 on Windows 10