Page 1 of 1

[Solved] Copy certain rows from one sheet to another

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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   Expand viewCollapse view
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

PostPosted: 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

PostPosted: 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   Expand viewCollapse view
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

PostPosted: Wed Apr 14, 2021 11:58 am
by JohnSUN-Pensioner
Try this
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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:
Code: Select all   Expand viewCollapse view
Option VBASupport 0

Re: Copy certain rows from one sheet to another

PostPosted: 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