Page 1 of 1
[Solved] Copy certain rows from one sheet to another
Posted: Wed Mar 24, 2021 2:28 pm
by Nelomf
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
Re: Copy certain rows from one sheet to another
Posted: Wed Mar 24, 2021 2:37 pm
by Villeroy
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.
Re: Copy certain rows from one sheet to another
Posted: Wed Mar 24, 2021 3:12 pm
by Nelomf
Sorry i should wrote OppenOffice basic. And this for private use, if someone can help i will appreciate but if not... pacience.
Re: Copy certain rows from one sheet to another
Posted: Wed Mar 24, 2021 3:40 pm
by Villeroy
You do not need any macro for this.
Just filter the source range before you copy and paste.
Re: Copy certain rows from one sheet to another
Posted: Wed Mar 24, 2021 5:02 pm
by Nelomf
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
Re: Copy certain rows from one sheet to another
Posted: Wed Mar 24, 2021 9:18 pm
by JeJe
Have you tried the macro recorder - not sure about the second part, but it might do the copy part for you?
Re: Copy certain rows from one sheet to another
Posted: Thu Mar 25, 2021 1:51 pm
by Nelomf
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())
Re: Copy certain rows from one sheet to another
Posted: Thu Mar 25, 2021 4:19 pm
by mcmurchy1917
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
Re: Copy certain rows from one sheet to another
Posted: Fri Mar 26, 2021 1:16 pm
by Nelomf
Thanks a lot, i will test it.
Manuel
Re: Copy certain rows from one sheet to another
Posted: Wed Apr 14, 2021 9:47 am
by Nelomf
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
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
Re: Copy certain rows from one sheet to another
Posted: Wed Apr 14, 2021 11:58 am
by JohnSUN-Pensioner
Try this
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
Call it from your code like as
Code: Select all
Sub TestFilter
FilterRangeToCell("encomenda.B3:F300","saida.A2")
End Sub
First param is address of source range and second - top-left cell of target range (BOTH AS STRING!)
Re: Copy certain rows from one sheet to another
Posted: Wed Apr 14, 2021 12:20 pm
by Nelomf
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
Re: Copy certain rows from one sheet to another
Posted: Wed Apr 14, 2021 12:35 pm
by JohnSUN-Pensioner
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.
Re: Copy certain rows from one sheet to another
Posted: Wed Apr 14, 2021 1:03 pm
by Nelomf
Hello
It works perfectly.
I'm going to see why it doesn't work on my flle and get back to you
Tks
Re: Copy certain rows from one sheet to another
Posted: Wed Apr 14, 2021 1:26 pm
by Villeroy
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
Disable VBA on top of the module:
Re: Copy certain rows from one sheet to another
Posted: Wed Apr 14, 2021 2:39 pm
by Nelomf
Ok.
I placed only your code separated in module 1, all the other code is in another sheet.
It is great.
Many thanks
Manuel