Copy all rows that contain certain text in a column to another sheet with button click

[Wim]

Hi everyone,

I've search the forum for answers but can't find a solution I need.

I have no idea how to code VBA for this. Basically the workbook is used to work out a quote and then I would like to save the scenario used to another sheet so that I can look back to see what scenario I used the last time for the same client. Cell K3 is a dropdown list with all the clients. All text in green can be different for each client and also be different for the same client for the next month.

See first image attached.

Actual workbook contains many more rows of data. If column R contains text then if a button is clicked, copy all the rows containing text in column R to the "scenario" sheet. But the data should only be added to the last empty row on this sheet. So previous data added to this sheet(scenario) is never deleted or overwritten.
The data that should be copied is the client(K3), todays date(B3), the data in column B, E, F, G, H, cell I4, I, J, cell R4 & R of the same row containing the text in column R.

The second image attached(scenario sheet) shows an example of how the data should be transfers and saved on the "scenario" sheet.

Hope this is clear to understand.

Any help will be appreciated.

Thanks
Wim

 

[bebo021999]

Could you post a sample sheet via XL2BB tool? Helpers dont have much time to retype the data

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Wim]

Sure

MAIN SHEET:

MAIN.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
3	16 05 2022									COFCO							MEI
4	TWST							R19.00	R3.21	3de LAASTE	DATUM	2de LAASTE	DATUM	LAASTE	DATUM	DIESEL	R22.21
5	Laaipunt	Afstand	Toll	%	mt	km/l	Formule	F14	DK	TARIEF	KWOTEER	TARIEF	KWOTEER	TARIEF	KWOTEER	AANPASSING	Tarief/ton
6	AFRIKASKOP	200	R21	40	34	1.85	R374	R323	R343					R310	29 09 2021	R310	R320
MAIN

Cell Formulas
Range	Formula
B3	B3	=TODAY()
J4	J4	=R4-I4
H6	H6	=IFERROR(SUM((C6*2/G6)*$R$4/(E6/100)/F6+D6), " ")
I6	I6	=IFERROR(SUM(($C6*2/$G6)*$I$4/($E6/100)/$F6+$D6), " ")
J6	J6	=IFERROR(SUM((($I6-$D6)+($C6*2/$G6*$J$4/$F6))+$D6), " ")
K6	K6	=@IF($M6="", "", LET(y,MATCH($C6,#REF!$A$7:$A$267,0),x, FILTER(#REF!$B$2:$DB$267,((#REF!$B$3:$DB$3=$L$4)*INDEX(#REF!$B$2:$DB$267,y+5,))<>0,""), IFERROR(INDEX(x, y+5,COLUMNS(x)-2),"")))
L6	L6	=IF(LET(y,MATCH($C6,#REF!$A$7:$A$267,0),x, FILTER(#REF!$B$2:$DB$267,((#REF!$B$3:$DB$3=$L$4)*INDEX(#REF!$B$2:$DB$267,y+5,))<>0,""), IFERROR(INDEX(x, 1,COLUMNS(x)-2),""))=$O6, "", LET(y,MATCH($C6,#REF!$A$7:$A$267,0),x, FILTER(#REF!$B$2:$DB$267,((#REF!$B$3:$DB$3=$L$4)*INDEX(#REF!$B$2:$DB$267,y+5,))<>0,""), IFERROR(INDEX(x, 1,COLUMNS(x)-2),"")))
M6	M6	=IF($O6="", "", LET(y,MATCH($C6,#REF!$A$7:$A$267,0),x, FILTER(#REF!$B$2:$DB$267,((#REF!$B$3:$DB$3=$L$4)*INDEX(#REF!$B$2:$DB$267,y+5,))<>0,""), IFERROR(INDEX(x, y+5,COLUMNS(x)-1),"")))
N6	N6	=IF(LET(y,MATCH($C6,#REF!$A$7:$A$267,0),x, FILTER(#REF!$B$2:$DB$267,((#REF!$B$3:$DB$3=$L$4)*INDEX(#REF!$B$2:$DB$267,y+5,))<>0,""), IFERROR(INDEX(x, 1,COLUMNS(x)-1),""))=$Q6, "", LET(y,MATCH($C6,#REF!$A$7:$A$267,0),x, FILTER(#REF!$B$2:$DB$267,((#REF!$B$3:$DB$3=$L$4)*INDEX(#REF!$B$2:$DB$267,y+5,))<>0,""), IFERROR(INDEX(x, 1,COLUMNS(x)-1),"")))

Cells with Data Validation
Cell	Allow	Criteria
K3	List	KLIENT, SEABOARD, UNIGRAIN, LDC, BESTER, TIGER, GRAINOVATION, BRISEN, COFCO, FLOTANK, AMEROPA, WILMAR, OLAM, GRAINVEST, AFGRI, SENWES, BELVIDERE, CARGILL

SCENARIO SHEET:

MAIN.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
2	Client	Date	Laaipunt	%	mt	km/l	Formule	F14	DK Diesel	DK	Diesel Prys	TARIEF
3	COFCO	16 05 2022	AFRIKASKOP	40	34	1.85	R374	R323	R19.00	R343	R22.21	R320
SCENARIOS