I have two workbooks, GNGInvLO.ods and GNGInvDBaseLO.ods.
The 1st workbook [GNGInvLO.ods] is an invoice template where information is collected to create invoices.
The orange high lighted cells indicate the data to be collected. {see picture below}
When the invoice is completed, I mouse click on the “TRANSFER” button on the right and below the Invoice #.
This sets in motion a macro [My thanks to and written by JohnSUN]
[see Best way to transfer data from one workbook/sheet to another on a reoccurring basis. ]
that transfers the data to the 2nd workbook [GNGInvDBaseLO.ods] [see picture below]
It seems to work very well, although I had to move my TOTALS to another sheet. which is OK for now.
The very small problem I have is one piece of data is being missed. Cell A7 which is Client name [test] on GNGInvLO.ods is not being transferred to workbook2 [GNGInvDBaseLO.ods]
As of yet, I do not know enough about macros in LibreOffice to see where [in the macro] this is being missed.
Below is the macro provided by JohnSUN, complete with commentary (Rem):
**Sub CommandButton1_Click()
Dim oDoc As Object, iSheet As Object, oSheet As Object, oCursor As Object
Dim iData As Variant,oData As Variant,aRowNums As Variant
Dim nNewRowNum As Long, i As Integer
GlobalScope.BasicLibraries.LoadLibrary("Tools")
Rem Rows with data in column D instead Range("D49"),Range("D6"),Range("D4"), etc
aRowNums = Array(49, 6, 4, 41, 34, 35, 32, 33, 36, 37, 38, 39, 40, 7)
iSheet = ThisComponent.getSheets().getByName("Service Invoice")
Rem Get all data from column D
iData = iSheet.getCellRangeByName("D1:D49").getDataArray()
Rem Take only the data from the specified rows
ReDim oData(0 To UBound(aRowNums))
For i = 0 To UBound(aRowNums)
oData(i) = iData(aRowNums(i)-1)(0)
Next i
Rem Output workbook (spreadsheet)
oDoc = OpenDocument(ConvertToURL("/home/DataStore/GNGRenoDebian/Macro/GNGInvDBase.ods"), Array())
oSheet = oDoc.getSheets().getByName("Tracking")
Rem Number of first empty row
oCursor = oSheet.createCursor()
oCursor.GotoEndOfUsedArea(True)
nNewRowNum = oCursor.RangeAddress.EndRow + 2
Rem Paste collected data
oSheet.getCellRangeByName("A" & nNewRowNum & ":N" & nNewRowNum).setDataArray(Array(oData))
Rem Store result and close output workbook
oDoc.store()
Rem oDoc.Close (True) this closes sheet instantaneously
End Sub**
So, please, if someone could point out to me where, in the macro, the cell A7 from GNGInvLO.ods gets copied to the 2nd workbook [GNGInvDBaseLO.ods]?