[Solved] Copy certain rows from one sheet to another
[Solved] Copy certain rows from one sheet to another
Hello
I have a calc workbook with 2 sheets, first is "encomenda" and second is "saida"
In the first sheet i have a table with the range B3:F300
I want to paste this table to the second sheet, range A2:E299, but only the rows that have values in the E column.
This must be done in OpenOffice basic because this is part of a procedure that i've already created..
I'm not a programmer and i need help.
Thanks in advance
Manuel
I have a calc workbook with 2 sheets, first is "encomenda" and second is "saida"
In the first sheet i have a table with the range B3:F300
I want to paste this table to the second sheet, range A2:E299, but only the rows that have values in the E column.
This must be done in OpenOffice basic because this is part of a procedure that i've already created..
I'm not a programmer and i need help.
Thanks in advance
Manuel
Last edited by Nelomf on Wed Apr 14, 2021 2:42 pm, edited 2 times in total.
Openoffice 4, Windows 7
Re: Copy certain rows from one sheet to another
If you have to do this in VBA then you need MS Excel.
If you are not a VBA programmer, then you have to hire one.
If you are not a VBA programmer, then you have to hire one.
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: Copy certain rows from one sheet to another
Sorry i should wrote OppenOffice basic. And this for private use, if someone can help i will appreciate but if not... pacience.
Openoffice 4, Windows 7
Re: Copy certain rows from one sheet to another
You do not need any macro for this.
Just filter the source range before you copy and paste.
Just filter the source range before you copy and paste.
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: Copy certain rows from one sheet to another
Yes i Know That.
But as i said before this code will be part of a greater macro.
It should copy and paste and then generate a PDF to send to a specific email all without operator handling.
Only the copy and paste is not made.
The table as 300 entries and only a few rows have all the columns with values.
Sorry for the english
But as i said before this code will be part of a greater macro.
It should copy and paste and then generate a PDF to send to a specific email all without operator handling.
Only the copy and paste is not made.
The table as 300 entries and only a few rows have all the columns with values.
Sorry for the english
Openoffice 4, Windows 7
Re: Copy certain rows from one sheet to another
Have you tried the macro recorder - not sure about the second part, but it might do the copy part for you?
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Re: Copy certain rows from one sheet to another
Yes i have tried and i have mange to copy the content. Thanks
Now another way of doing what i want is to filter the copied content by not empty results in one of the columns.
I've tried recording a macro for that but this is the result (it made the filter but did not record de args)
sub filtrar
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DataFilterStandardFilter", "", 0, Array())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:FilterExecute", "", 0, Array())
rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:Undo", "", 0, Array())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Save", "", 0, Array())
Now another way of doing what i want is to filter the copied content by not empty results in one of the columns.
I've tried recording a macro for that but this is the result (it made the filter but did not record de args)
sub filtrar
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DataFilterStandardFilter", "", 0, Array())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:FilterExecute", "", 0, Array())
rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:Undo", "", 0, Array())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Save", "", 0, Array())
Openoffice 4, Windows 7
-
- Posts: 23
- Joined: Fri Feb 22, 2013 2:15 pm
Re: Copy certain rows from one sheet to another
I would use something like this. You will have to edit it to suit your own requirements. It's untested.
Code: Select all
Sub Copy_Cells
Dim iEndCol As Integer
Dim dblEndRow As Double
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
Dim CellAddress As New com.sun.star.table.CellAddress
oDoc = ThisComponent
oController= ThisComponent.getCurrentController()
oFirstSheet = oDoc.Sheets.getByIndex( 0 )
iEndCol = getLastUsedColumn(oFirstSheet)
dblEndRow = getLastUsedRow(oFirstSheet)
REM Copy the row/columns from first sheet to a second sheet
oSecondSheet = oDoc.createInstance("com.sun.star.sheet.Spreadsheet")
oDoc.Sheets.insertByName("Working Sheet", oSecondSheet)
CellRangeAddress.Sheet = 0
CellRangeAddress.StartColumn = 0
CellRangeAddress.StartRow = 0
CellRangeAddress.EndColumn = iEndCol
CellRangeAddress.EndRow = dblEndRow
CellAddress.Sheet = 1
CellAddress.Column = 0
CellAddress.Row = 0
oFirstSheet.copyRange(CellAddress, CellRangeAddress)
End Sub
Slackware user
Re: Copy certain rows from one sheet to another
Thanks a lot, i will test it.
Manuel
Manuel
Openoffice 4, Windows 7
Re: Copy certain rows from one sheet to another
I've finally had the chance to test this code but i got an error
Problem: Error in "iEndCol = getLastUsedColumn(oFirstSheet)" Basic execution erro
subprocedure or function not defined
Problem: Error in "iEndCol = getLastUsedColumn(oFirstSheet)" Basic execution erro
subprocedure or function not defined
mcmurchy1917 wrote:I would use something like this. You will have to edit it to suit your own requirements. It's untested.
Code: Select all
Sub Copy_Cells Dim iEndCol As Integer Dim dblEndRow As Double Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress Dim CellAddress As New com.sun.star.table.CellAddress oDoc = ThisComponent oController= ThisComponent.getCurrentController() oFirstSheet = oDoc.Sheets.getByIndex( 0 ) iEndCol = getLastUsedColumn(oFirstSheet) dblEndRow = getLastUsedRow(oFirstSheet) REM Copy the row/columns from first sheet to a second sheet oSecondSheet = oDoc.createInstance("com.sun.star.sheet.Spreadsheet") oDoc.Sheets.insertByName("Working Sheet", oSecondSheet) CellRangeAddress.Sheet = 0 CellRangeAddress.StartColumn = 0 CellRangeAddress.StartRow = 0 CellRangeAddress.EndColumn = iEndCol CellRangeAddress.EndRow = dblEndRow CellAddress.Sheet = 1 CellAddress.Column = 0 CellAddress.Row = 0 oFirstSheet.copyRange(CellAddress, CellRangeAddress) End Sub
Openoffice 4, Windows 7
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Copy certain rows from one sheet to another
Try this
Call it from your code like as
First param is address of source range and second - top-left cell of target range (BOTH AS STRING!)
Code: Select all
Sub FilterRangeToCell(sDataAddress As String, sTargetAddress As String)
Dim oSheets As Variant
Dim oCellRangesByName As Variant
Dim oFilteredRange As Variant
Dim oTargetCellAddr As New com.sun.star.table.CellAddress
Dim oFilterDescriptor As Variant
Dim oFilterFields(0) As New com.sun.star.sheet.TableFilterField
oSheets = ThisComponent.getSheets()
oCellRangesByName = oSheets.getCellRangesByName(sDataAddress)
oFilteredRange = oCellRangesByName(0)
oCellRangesByName = oSheets.getCellRangesByName(sTargetAddress)
oTargetCellAddr = oCellRangesByName(0).getCellByPosition(0, 0).getCellAddress()
oCellRangesByName(0).Spreadsheet().getCellRangeByPosition(oTargetCellAddr.Column, oTargetCellAddr.Row, _
oTargetCellAddr.Column + oFilteredRange.getColumns().getCount(), _
oTargetCellAddr.Column + oFilteredRange.getRows().getCount()).ClearContents(-1)
oFilterDescriptor = oFilteredRange.createFilterDescriptorByObject(oFilteredRange)
oFilterDescriptor.OutputPosition = oTargetCellAddr
oFilterDescriptor.CopyOutputData = True
oFilterDescriptor.ContainsHeader = False ' If first row is header then set to TRUE
oFilterFields(0).Operator = com.sun.star.sheet.FilterOperator.NOT_EMPTY
oFilterFields(0).Field = 3 ' Column E
oFilterDescriptor.setFilterFields(oFilterFields)
oFilteredRange.filter(oFilterDescriptor)
End Sub
Code: Select all
Sub TestFilter
FilterRangeToCell("encomenda.B3:F300","saida.A2")
End Sub
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: Copy certain rows from one sheet to another
First of all, tks a lot.
I've tried and for what i can understand (that's not a lot ) this code wilkl act as a filter as i want.
I've placed a form button on my encomenda sheet and call the Sub TestFilter with that.
I've got the following error
Basic sintaxe error (i'm translating from portuguese)
Espected: =.
and highlight "encomenda.B3:F300"
Thanks
I've tried and for what i can understand (that's not a lot ) this code wilkl act as a filter as i want.
I've placed a form button on my encomenda sheet and call the Sub TestFilter with that.
I've got the following error
Basic sintaxe error (i'm translating from portuguese)
Espected: =.
and highlight "encomenda.B3:F300"
Thanks
Openoffice 4, Windows 7
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Copy certain rows from one sheet to another
Hmm... I also added a button on the sheet and assigned the same macro to it. Please check if the filtering works in this spreadsheet.
- Attachments
-
- CopyFilteredRange.ods
- Example ods
- (25.99 KiB) Downloaded 1436 times
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: Copy certain rows from one sheet to another
Hello
It works perfectly.
I'm going to see why it doesn't work on my flle and get back to you
Tks
It works perfectly.
I'm going to see why it doesn't work on my flle and get back to you
Tks
Openoffice 4, Windows 7
Re: Copy certain rows from one sheet to another
Disable VBA on top of the module:Nelomf wrote:Hello
It works perfectly.
I'm going to see why it doesn't work on my flle and get back to you
Tks
Code: Select all
Option VBASupport 0
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: Copy certain rows from one sheet to another
Ok.
I placed only your code separated in module 1, all the other code is in another sheet.
It is great.
Many thanks
Manuel
I placed only your code separated in module 1, all the other code is in another sheet.
It is great.
Many thanks
Manuel
Openoffice 4, Windows 7