Hello @Snake3y3s,
EDIT 2017-09-09
i tried to translate your code into this:
EDIT 2017-09-12 updated code
EDIT 2017-09-14 update code 2
Sub SWD_AwakeningsTrade()
Const sSheetName_CardList As String = "Card_List"
Const sSheetName_ForTrade As String = "Awakenings_For_Trade"
Dim oSheets As Object
oSheets = ThisComponent.Sheets
Dim oSheet_Card_List As Object
If oSheets.hasByName( sSheetName_CardList ) Then
oSheet_Card_List = oSheets.getByName( sSheetName_CardList )
Else
oSheet_Card_List = oSheets.getByIndex( 0 )
End If
Dim oSheet_For_Trade As Object
If Not oSheets.hasByName( sSheetName_ForTrade ) Then
oSheets.insertNewByName( sSheetName_ForTrade, 99 ) REM Insert new sheet at end.
End If
oSheet_For_Trade = oSheets.getByName( sSheetName_ForTrade )
oSheet_For_Trade.getCellRangeByName( "A3:K176" ).clearContents( 1023 )
Dim oCursor As Object
oCursor = oSheet_Card_List.createCursor()
oCursor.gotoStartOfUsedArea( False )
oCursor.gotoOffset( 0, 2 ) REM Exclude 2 header rows.
oCursor.collapseToSize ( 10, 176 )
Dim oRows as Object, oRow as Object, oCell As Object, lCount As Long
lCount = 2 REM leave 2 rows empty for header.
oRows = oCursor.getRows().createEnumeration() REM Copy tradable rows:
Do While oRows.hasMoreElements()
oRow = oRows.nextElement()
If oRow.getCellByPosition( 10, 0 ).getValue() > 0 Then REM Check column K (column index=10)
oCell = oSheet_For_Trade.getCellByPosition( 0, lCount )
oSheet_Card_List.copyRange( oCell.CellAddress , oRow.getRangeAddress() )
lCount = lCount + 1
End If
Loop
End Sub
Sub SWD_AwakeningsNeed()
Const sSheetName_CardList As String = "Card_List"
Const sSheetName_NeedCard As String = "Awakenings_Need"
Dim oSheets As Object
oSheets = ThisComponent.Sheets
Dim oSheet_Card_List As Object
If oSheets.hasByName( sSheetName_CardList ) Then
oSheet_Card_List = oSheets.getByName( sSheetName_CardList )
Else
oSheet_Card_List = oSheets.getByIndex( 0 )
End If
Dim oSheet_Need_Cards As Object
If Not oSheets.hasByName( sSheetName_NeedCard ) Then
oSheets.insertNewByName( sSheetName_NeedCard, 99 ) REM Insert new sheet at end.
End If
oSheet_Need_Cards = oSheets.getByName( sSheetName_NeedCard )
oSheet_Need_Cards.getCellRangeByName( "A3:K176" ).clearContents( 1023 )
Dim oCursor As Object
oCursor = oSheet_Card_List.createCursor()
oCursor.gotoStartOfUsedArea( False )
oCursor.gotoOffset( 0, 2 ) REM Exclude 2 header rows.
oCursor.collapseToSize ( 10, 176 )
Dim oRows as Object, oRow as Object, oCell As Object, lCount As Long
lCount = 2 REM leave 2 rows empty for header.
oRows = oCursor.getRows().createEnumeration() REM Copy tradable rows:
Do While oRows.hasMoreElements()
oRow = oRows.nextElement()
If oRow.getCellByPosition( 9, 0 ).getString() = "NO" Then REM Check column J (column index=9)
oCell = oSheet_Need_Cards.getCellByPosition( 0, lCount )
oSheet_Card_List.copyRange( oCell.CellAddress , oRow.getRangeAddress() )
lCount = lCount + 1
End If
Loop
End Sub
Sub SWD_SpiritTrade()
Const sSheetName_CardList As String = "Card_List"
Const sSheetName_ForTrade As String = "Spirit of the Rebellion_For_Trade"
Dim oSheets As Object
oSheets = ThisComponent.Sheets
Dim oSheet_Card_List As Object
If oSheets.hasByName( sSheetName_CardList ) Then
oSheet_Card_List = oSheets.getByName( sSheetName_CardList )
Else
oSheet_Card_List = oSheets.getByIndex( 0 )
End If
Dim oSheet_For_Trade As Object
If Not oSheets.hasByName( sSheetName_ForTrade ) Then
oSheets.insertNewByName( sSheetName_ForTrade, 99 ) REM Insert new sheet at end.
End If
oSheet_For_Trade = oSheets.getByName( sSheetName_ForTrade )
oSheet_For_Trade.getCellRangeByName( "A3:K176" ).clearContents( 1023 )
Dim oCursor As Object
oCursor = oSheet_Card_List.createCursor()
oCursor.gotoStartOfUsedArea( False )
oCursor.gotoOffset( 0, 179 ) REM Exclude 2 header rows.
oCursor.collapseToSize ( 10, 176 )
Dim oRows as Object, oRow as Object, oCell As Object, lCount As Long
lCount = 2 REM leave 2 rows empty for header.
oRows = oCursor.getRows().createEnumeration() REM Copy tradable rows:
Do While oRows.hasMoreElements()
oRow = oRows.nextElement()
If oRow.getCellByPosition( 10, 0 ).getValue() > 0 Then REM Check column K (column index=10)
oCell = oSheet_For_Trade.getCellByPosition( 0, lCount )
oSheet_Card_List.copyRange( oCell.CellAddress , oRow.getRangeAddress() )
lCount = lCount + 1
End If
Loop
End Sub
Sub SWD_SpiritNeed()
Const sSheetName_CardList As String = "Card_List"
Const sSheetName_NeedCard As String = "Spirit of the Rebellion_Need"
Dim oSheets As Object
oSheets = ThisComponent.Sheets
Dim oSheet_Card_List As Object
If oSheets.hasByName( sSheetName_CardList ) Then
oSheet_Card_List = oSheets.getByName( sSheetName_CardList )
Else
oSheet_Card_List = oSheets.getByIndex( 0 )
End If
Dim oSheet_Need_Cards As Object
If Not oSheets.hasByName( sSheetName_NeedCard ) Then
oSheets.insertNewByName( sSheetName_NeedCard, 99 ) REM Insert new sheet at end.
End If
oSheet_Need_Cards = oSheets.getByName( sSheetName_NeedCard )
oSheet_Need_Cards.getCellRangeByName( "A3:K176" ).clearContents( 1023 )
Dim oCursor As Object
oCursor = oSheet_Card_List.createCursor()
oCursor.gotoStartOfUsedArea( False )
oCursor.gotoOffset( 0, 179 ) REM Exclude 2 header rows.
oCursor.collapseToSize ( 10, 176 )
Dim oRows as Object, oRow as Object, oCell As Object, lCount As Long
lCount = 2 REM leave 2 rows empty for header.
oRows = oCursor.getRows().createEnumeration() REM Copy tradable rows:
Do While oRows.hasMoreElements()
oRow = oRows.nextElement()
If oRow.getCellByPosition( 9, 0 ).getString() = "NO" Then REM Check column J (column index=9)
oCell = oSheet_Need_Cards.getCellByPosition( 0, lCount )
oSheet_Card_List.copyRange( oCell.CellAddress , oRow.getRangeAddress() )
lCount = lCount + 1
End If
Loop
End Sub
HTH, lib