Ask Your Question

Calc, Copy a certain value from one sheet to another

My spreadsheet knowledge is really tiny, the last time i did advanced functions was in primary school.

I am trying to make a spreadsheet for a Card game to make sorting my collection easier.

So far I need it to show after a certain value if I have any to trade. so I found a =MAX(0,A14-B14) function (where the first column is the number I have and the second is the max amount I need) which will show me how many of that card I have to trade.

I have another row with a IF function showing if that card is complete returning a "yes" or a "no" (=IF (A14>=B12, "yes", "no"))

Now, I would like one more function. I need to be able to take the ROW of the card I have for trade and put it in a new sheet, based on if the collection is complete AND the number I have for trade is greater than 0, removing any spaces between rows.

I have seen something like this done using an advanced visual basic parameter, but im sure that this could be done using normal spreadsheet functionality.

Can anyone help me out?

as a bonus, I would also like to take cards that are not complete and put those in a new sheet.

https://ask.libreoffice.org/upfiles/1...

In the attached File you can see what i have so far... it reads count vs max and outputs a yes or no if complete, then tells me how many I have extra for trade.

I want to now do a check on if it is complete and I have trades (so if row J shows "yes" AND the For Trade number equal to or higher than 1 in row K)

I want it to then take that row and put it in a new sheet.

thanks

!!UPDATE!!

Ok, I have decided to try do this using a macro button that runs a Libre office basic script, problem is that all i can find are scripts for Open office basic and excel and I am not sure how to convert them to Librebasic.

Tried asking on the Stackoverflow page, but the one answer I get is of a guy just basically saying "yup... that wont work..." without any constructive help.

This is what i have so far...

Option Compatible
Sub SWD_AwakeningsTrade
Dim i, LastRow
LastRow = Sheets("Card_List").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Awakenings For trade").range("A2:I500").clearContents
For i=2 to LastRow
If Sheets("Card_List").Cells(i,"K").Value = "(>1)" Then
Sheets("Card_List").Cells(i, "K").EntireRow.Copy
Destination:=Sheets("Awakenings For trade").Range("A" & Rows.Count.end(xlUp)
End if
next i
End Sub


I keep getting a Syntax errors

anyone familiar with Librebasic?

edit retag close merge delete

Comments

Think that your question is too involving. Try to simplify. From my experience, shorter question has better answer rate. You might want to use PIVOT TABLES or VLOOKUP function in way that suit your need for completing your task successfully. Maybe here you'll find answer: https://forum.openoffice.org/en/forum...

( 2017-07-24 15:39:15 +0200 )edit

I added Code tags to make your script more readable. It would help if you could advise the line where the error occurs, and the exact text of the error.

( 2017-09-09 05:29:10 +0200 )edit

i edited my previous answer, including a macro that hopefully works for you

( 2017-09-09 06:25:04 +0200 )edit

3 Answers

Sort by » oldest newest most voted

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 ...
more

Comments

AWESOME!!!! Thank you, thank you, thank you!! its working :D

Ok, I'm trying to understand the code and have somewhat of an idea of how it is working. I would like to add a few more buttons performing similar functions.

P.S. i hate these comments character limits

( 2017-09-12 18:23:04 +0200 )edit

Basically, I want to have my whole collection on the main sheet (as it is now) then a button outputting what i have for trade (as the above code does) but I need the sheet name to be Awakenings for trade.

Am I correct in changing line 14 & 15 "For_Trade" to "Awakenings_For_Trade" ? and then updating any lines related to that tag?

( 2017-09-12 18:23:56 +0200 )edit

I need the button only to check from A3:K176 (im not sure where the top code is reading from and to) how does it know to check the value in column K?? is that in line 17? Also I need it to start pasting from row A3 in the new page (so I can add the awakenings header to the "for trade sheet"

( 2017-09-12 18:31:20 +0200 )edit

Once that is all done, I would like another button (or could even use the same button, just thought another button would make coding a little easier) that would check the "count" column (the A column) and if it has a value of 0, output to a new sheet called "Awakenings_Need"

( 2017-09-12 18:33:25 +0200 )edit

Once that is all done, I will need to add another 2 buttons performing a very similar function to the previous 2, but outputs to 2 new sheets. these new buttons would now check from A178:K(wherever the next set ends) and do the same function as the above but outputting to a new sheet called "SOR_For_Trade" or "Spirit_For_Trade" and then the other button for performing the "SOR_Need" function.

( 2017-09-12 18:37:22 +0200 )edit

Maybe we could discuss this over a PM or email? not sure how or where to do that on this site though...

( 2017-09-12 18:42:37 +0200 )edit

You're welcome @Snake3y3s:),

i updated the macro in my answer. changed the sheet name "For_Trade" into "Awakenings_For_Trade", and start pasting from cell A3.

The check for column K happens in the line If oRow.getCellByPosition( 10, 0 ).getValue() > 0 Then. ( column index 10 = K)

i also added a method SWD_AwakeningsNeed() for the "Needed Cards". The Spirit sheets could be easily copied and adapted from these 2.

Just leave at least 1 row empty between the SWD and Spirit area.

( 2017-09-12 22:35:31 +0200 )edit

Wow... this is awesome, thank you very much... I really appreciate the help. you asked "For the "Needed Cards": (should the row for Luke Skywalker really be excluded ??)" im not following where that would be excluded...

( 2017-09-12 23:15:32 +0200 )edit

Well, since you wanted to check only from A3:K176, and the row with Luke Skywalker is row 2 in the example file that you uploaded before.

( 2017-09-12 23:21:36 +0200 )edit

oh, that was just as an example file. in row 2 I added a header I merged cells A to K and added "Awakenings" going to do the same for the next set, but add a header called "Spirit of the Rebelion"

what is the purpose of the value 1023 in "clearContents( 1023 )"

( 2017-09-13 00:05:45 +0200 )edit

I have gotten most of the functionality working now... except the copy to other sheets.

so I would like to get that working. Can anyone assist me?

C:\fakepath\destiny test.ods

In the attached File you can see what i have so far... it reads count vs max and outputs a yes or no if complete, then tells me how many I have extra for trade.

I want to now do a check on if it is complete and I have trades (so if row J shows "yes" AND the For Trade number equal to or higher than 1 in row K)

I want it to then take that row and put it in a new sheet.

more

Comments

Please edit your question, don't use answers for that.

( 2017-07-27 22:43:23 +0200 )edit

If you just want a quick way to see which sets are complete, or which sets have tradable cards, you can just use an autofilter on the appropriate column on that first sheet.

Highlight the column you want to filter, select Menu/Data/Autofilter, then select the little drop-down arrow that now appears at the top of that column, and adjust away.

(Menu/Data/Autofilter again to turn it off again.)

For example, if you just want to see sets with tradable cards, highlight column K, turn on autofilter, select the drop-down and uncheck "0", then click OK.

Or to see only the incomplete sets, highlight column J, turn on autofilter, select the drop-down and uncheck "Yes", then click OK.

more

Stats

Asked: 2017-07-23 22:31:38 +0200

Seen: 1,128 times

Last updated: Sep 14 '17