Calc, Copy a certain value from one sheet to another

English
, , , ,
#1

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/15011866137338886.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.

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?

#2

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: If condition is met copy entire row of data to another sheet (View topic) • Apache OpenOffice Community Forum

#3

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.

#4

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

#5

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

#6

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

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

#7

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?

#8

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”

#9

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”

#10

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.

#11

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

#12

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.

#13

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…

#14

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.

#15

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

#16

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

#17

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

#18

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 :heavy_check_mark: icon on the upperleft side of the answer.

#19

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

#20

Thank you so much for your time and help.
It seems to work perfectly :smiley:
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?

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.