Calc, Copy a certain value from one sheet to another

[Snake3y3s]

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”

[Snake3y3s]

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”

[Snake3y3s]

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.

[Snake3y3s]

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

[librebel]

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.

[Snake3y3s]

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…

[librebel]

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.

[Snake3y3s]

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 )”

[librebel]

1023 stands for all possible content types to delete, it is the sum of 512+256+128+64+32+16+8+4+2+1 , constants of type com.sun.star.sheet.CellFlags

[Snake3y3s]

@librebel found one error.
for the “needed” macro, after I added the “spirit of rebellion” set underneath the Awakenings set, skipping one row, it seems to copy all the rows with a Column A value of 0 to the new sheet, not only from A3:K176. (the awakenings set)
Also, instead of using the 0 value from the first row to check if its complete… could we rather make it check the “Complete” column if it has a NO. (Column J)

[librebel]

of course… the second set has another starting row.

i suggest you make a separate question for it, since this question is solved already,

Please mark the above answer as correct by clicking on the icon on the upperleft side of the answer.

[librebel]

i have updated the 2 earlier macros for the Awakenings cards,
Hope these all work OK now,

i also added the 2 new macros for the second set of cards here, since they are so similar.

Cheers, lib

[Snake3y3s]

Thank you so much for your time and help.
It seems to work perfectly
still need to add the Spirit set, which I am busy with, then I will add those buttons in.
in the code, what does “oCursor.collapseToSize ( 10, 176 )” do?
… does it take the initial cursor setting , in this case column A row 2 “(0, 2)” and selects all the way down to column K row 176?

[librebel]

Great to read it works.

The nomenclatura of the XSheetCellCursor methods can be confusing sometimes…

.collapseToSize( nColumns, nRows ) changes the size of the cursor range so that it has nColumns columns and nRows rows.

[Snake3y3s]

For the spirit code…
Does the line “oCursor.gotoOffset( 0, 179 )” indicate where to start doing the lookup?

[librebel]

yes, it moves the xCellCursor to the starting point of the Spirit rows to be copied. The passed Column and Row offsets are relative to the current cursor position.

[Snake3y3s]

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?

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.

[mariosv]

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

[Paul451]

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.