Macro copying from data sheet to result sheet

[haraldthomsen]

Hi
I have an Excel macro, but cannot get it to work in OpenOffice.
From the data sheet I need to copy rows with items from one or more suppliers.
Each supplier have a specific number. The number of items differ from each supplier.

I have seen Villeroy's typefilter, but I sure how to modify it to my needs.

Kind regards,
Harald

Sub TopSupplierFilter()

Sheets("Data").Range("A2").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Result").Range("D4:D10"), _
CopyToRange:=Sheets("Result").Range("A20:N20") _
, Unique:=False
End Sub

[FJCC]

Without seeing how your data are laid out and without knowing anything about Excel, I had to make some guesses about what your macro does. Something like this might work for you.

Code: Select all

oDataSheet = ThisComponent.Sheets.getByName("Data")
oResultSheet = ThisComponent.Sheets.getByName("Result")

'Find what cells are used in the region starting at Data.A2
oCellA2 = oDataSheet.getCellrangeByName("A2")
oCurs = oDataSheet.createCursorByRange(oCellA2)
oCurs.collapseToCurrentRegion()

oDataRng = oDataSheet.getCellrangeByName(oCurs.AbsoluteName)
oCritRng = oResultSheet.getCellrangeByName("D4:D10")
oOutCell = oResultSheet.getCellrangeByName("A20")
OutCellAddr = oOutCell.CellAddress

Desc = oCritRng.createFilterDescriptorByObject(oDataRng)
Desc.CopyOutputData = True
Desc.OutputPosition = OutCellAddr

oDataRng.filter(Desc)

[haraldthomsen]

Hi FJCC
I will try and be more specific.

Datasheet
Sup Item
1 1a
1 1b
1 1c
2 2a
2 2b
3 3a
3 3b
3 3c
4 4a
4 4b

Resultsheet
If I write 1 in D4 and 4 in D5 and leave cells D6 to D10 blank
the result should be (Starting in cell A20)
1 1a
1 1b
1 1c
4 4a
4 4b

Thank you very much for your help.

Kind regards,
Harald

[Villeroy]

I have seen Villeroy's typefilter, but I sure how to modify it to my needs.

Nobody but me understands what you've seen without providing a hyperlink. It is this one with an attached document and a detailed description viewtopic.php?t=1049&p=4531#p4531 In fact, this was my first attachment on this forum site.

[Villeroy]

That code can be used in many different ways. All it does is that it refreshes a named database range (menu:Data>Define...) by the filter criteria in a named cell range (menu:Insert>Names>Define...).
A text box provides a "text changed" event which is triggered with every key stroke. In the example document, the database range is filled from a true database. As a side effect, the same code pulls up-to-date data from the database into the sheet with every refresh. But it works as well with database ranges that are not linked to a true database.

The text goes to a linked cell, the linked cell is referenced by a formula in the criteria range and the database range is set up with an "advanced filter" which reads filter criteria from a cell range. See menu:>Data>Filter>Advanced... where you can set up the details, such as an output range where to copy the filtered results and a "no duplicates" option. Excel has the same feature.
With every keystroke into the text box the macro does the same as you would do calling menu:Data>Refresh with the cell cursor in the database range.
The linked cell (D2 or "linked_cell" in my document) does not need to be named.

Copy the code to your own document.
Modify the 2 names of the database range and the named criteria range in the Basic editor. The rest of the code remains untouched.
Apply an advanced filter and adjust the advanced filter settings to your needs, e.g. output to other sheet and pattern matching by "regular expressions".
Add text boxes with linked cells and concatenate the entered criteria in your named criteria range. The regex equivalent of a * wildcard is .* (dot and asterisk)