Send cell values to 2 other sheets according to condition

[leobrice]

Greetings friends of MREXCEL, good evening.

The file is used to calculate costs of products and services, and the macros automate the process of calculations and procedures, however, has a detail and I have not been able to solve it, I explain the operation:

SHEET COSTOS PRODUCTOS IMPORTADOS:
In it the information of international products and services is registered, in it is placed:
A= Product or Service Name
B= IMPORTADO
C= Unit of Measurement
D= Country of Origin
E= Currency
F= Product Quantity
G= Cost per Unit
H= Exchange Rate
I= Formula
J= Expenses
K= Expense
L= Expense
M= Expense
N= Expense
O= Expense
P= Expense
R= Expense
S= Expense
T= Expense
U= Formula
V= SI/NO (HERE MACRO IS ACTIVATED WHEN SI IS ENTERED)
W= Formula
X= Formula

The following information is required to be sent to 2 sheets:

1)
COSTOS PRODUCTOS IMPORTADOS (A) to sheet PRECIOS PRODUCTOS Y SERVICIOS (A)
COSTOS PRODUCTOS IMPORTADOS (B) to sheet PRECIOS PRODUCTOS Y SERVICIOS (B)
COSTOS PRODUCTOS IMPORTADOS (C) to sheet PRECIOS PRODUCTOS Y SERVICIOS (C)
COSTOS PRODUCTOS IMPORTADOS (W) to sheet PRECIOS PRODUCTOS Y SERVICIOS (D)

2)
COSTOS PRODUCTOS IMPORTADOS (A) to sheet PUNTO DE EQUILIBRIO (A)

Notes:
- The macro is activated by placing SI in column V of COSTOS PRODUCTOS IMPORTADOS.
- After running the macro, the active sheet should be PRECIOS PRODUCTOS Y SERVICIOS.
- The cursor of COSTOS PRODUCTOS IMPORTADOS should be in column A, below the last product registered.
- The cursor of PRECIOS PRODUCTOS Y SERVICIOS should be in column E of the product that is sent with the macro.
- The cursor for PUNTO DE EQUILIBRIO should be in column B of the product that is sent with the macro.
The macro must allow to modify amounts of a product registered in COSTOS PRODUCTOS IMPORTADOS without running the macro again, since it exists and has been previously sent to the 2 sheets mentioned above and the new amount of column W must update the amount of column D of PRECIOS PRODUCTOS Y SERVICIOS.

Thank you in advance for your help

Translated with DeepL.com (free version)

CALCULADORA DE PRECIOS Y COSTOS DATCHEL PLUS1.xlsm
	A		C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Piscinas Aqualux S.A. DEFINITIVA
2	COSTOS DE PRODUCTOS Y SERVICIOS IMPORTADOS
3	PRODUCTO		UNIDAD DE MEDIDA (seleccionar)	PAÍS DE ORIGEN	MONEDA	CANTIDAD DE PRODUCTO	COSTO POR UNIDAD	TIPO DE CAMBIO	TOTAL COSTO PRODUCTO	ARANCELES	IMPUESTOS	SEGUROS	GASTOS DE NACIONALIZACION	TRANSPORTE NACIONAL	FLETES	ALMACENAJE O DEPÓSITO	OTROS CONCEPTOS 1	OTROS CONCEPTOS 2	OTROS CONCEPTOS 3	OTROS CONCEPTOS 4	TOTAL COSTO PRODUCTO NACIONALIZADO	CONFIRMAR INFORMACION	COSTO PRODUCTO UNITARIO
4	IMPORTADO								₡	₡	₡	₡	₡	₡	₡	₡	₡	₡	₡	₡	₡	(SI / NO)	₡
5	IMPORTADO A		Kilo	ESTADOS UNIDOS	U$	125,00	5,15	540,00	347.625,00	10,00	10,00	10,00	10,00	10,00	10,00	10,00	10,00	10,00	10,00	10,00	347.735,00	SI	2.781,88	por Kilo
6	IMP A		Kilo	ESTADOS UNIDOS	U$	100,00	3,15	540,00	170.100,00	10,00	10,00	10,00	10,00	10,00	10,00	10,00	10,00	10,00	20,00	10,00	170.220,00	SI	1.702,20	por Kilo
7					U$				0,00												0,00	NO	0,00	0
8					U$				0,00												0,00	NO	0,00	0
9					U$				0,00												0,00	NO	0,00	0
COSTOS PRODUCTOS IMPORTADOS

Cell Formulas
Range	Formula
A1	A1	='INFORMACION GENERAL'!D5
J4	J4	=MONEDAS!C2
K4	K4	=MONEDAS!C2
L4	L4	=MONEDAS!C2
M4	M4	=MONEDAS!C2
N4	N4	=MONEDAS!C2
O4	O4	=MONEDAS!C2
P4	P4	=MONEDAS!C2
Q4	Q4	=MONEDAS!C2
R4	R4	=MONEDAS!C2
S4	S4	=MONEDAS!C2
T4	T4	=MONEDAS!C2
W4	W4	=MONEDAS!C2
W5:W9	W5	=IF(V5="SI",IFERROR(U5/F5,0),0)
X5:X9	X5	=C5
I4	I4	=MONEDAS!C2
I5:I9	I5	=F5*G5*H5
U4	U4	=MONEDAS!C2
U5:U9	U5	=SUM(I5:T5)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
X5:X10000	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
U5:V9	Any value
A5:B10000	Custom	=CONTAR.SI($A$5:$A$10000;A5)<=1
C5:C10000	List	=UNIDADES
D5:D10000	List	=PAISES
E5:E10000	List	=MONEDAS

CALCULADORA DE PRECIOS Y COSTOS DATCHEL PLUS1.xlsm
	A	B	C	D	E
1	Piscinas Aqualux S.A. DEFINITIVA
2	PRECIO DE VENTA DE PRODUCTOS Y SERVICIOS
3	PRODUCTO / SERVICIO	PRODUCTO NACIONAL	UNIDAD DE MEDIDA	COSTO UNITARIO PRODUCTO	VENTAS ESTIMADAS MENSUALES UNIDADES
4		O
5		IMPORTADO		₡	10.020
6	IMPORTADO	IMPORTADO	Kilo	1.000,00	10.000,00
7	S1	NACIONAL	unidad	10.000.000,00	20,00
8	dsdfsd	NACIONAL		52.000,00
9	A	NACIONAL	Litro	1.875,00
10	B	NACIONAL	Kilo	11.555,56
PRECIOS PRODUCTOS Y SERVICIOS

Cell Formulas
Range	Formula
A1	A1	='INFORMACION GENERAL'!D5
D5	D5	=MONEDAS!C2
E5	E5	=SUM(E6:E9998)

Cells with Data Validation
Cell	Allow	Criteria
A6:A10	Custom	=CONTAR.SI($A$5:$A$9998;A6)<=1

CALCULADORA DE PRECIOS Y COSTOS DATCHEL PLUS1.xlsm
	A	B	C
1	Piscinas Aqualux S.A. DEFINITIVA
2	PUNTO DE EQUILIBRIO GLOBAL MENSUAL
3	INFORME MENSUAL	VENTAS MENSUALES ESTIMADAS Y/O REALES	VENTA PROYECTADA MENSUAL
4	MES / AÑO: 09/21	UNIDADES	UNIDADES
5	PRODUCTOS Y SERVICIOS TOTALES →	275	275
6	S1	75	75
7	S2	80	80
8	IMPORTADO	120	120
PUNTO DE EQUILIBRIO

Cell Formulas
Range	Formula
A1	A1	='INFORMACION GENERAL'!D5
A2	A2	=IF('COSTOS FIJOS'!A5="MENSUAL","PUNTO DE EQUILIBRIO GLOBAL MENSUAL","PUNTO DE EQUILIBRIO GLOBAL ANUAL")
A3	A3	=IF('COSTOS FIJOS'!A5="MENSUAL","INFORME MENSUAL","INFORME ANUAL")
C3	C3	=IF('COSTOS FIJOS'!A5="MENSUAL","VENTA PROYECTADA MENSUAL","VENTA PROYECTADA ANUAL")
B5:C5	B5	=SUM(B6:B10000)
C6:C8	C6	=IF('COSTOS FIJOS'!$A$5="MENSUAL",B6,B6*12)

Cells with Data Validation
Cell	Allow	Criteria
A6:A8	Custom	=CONTAR.SI($A$5:$A$10000;A6)<=1

 

[leobrice]

Hi @mumps how are you?

This is the other case I had told you about and I had told you it was very similar to the case you helped me solve. In fact, I used the final macro you made and made some changes to adapt it to this case.
I think it works fine, it sends the data from COSTS IMPORTED PRODUCTS to PRICES PRODUCTS AND SERVICES and to BALANCE POINT, but if you could check it please you handle very well the VBA topic.

The only thing I did not manage to do is to update the column D of PRICES PRODUCTS AND SERVICES, when the cost of the existing product varies in the sheet COSTS IMPORTED PRODUCTS.
Can you please help me. Thanks in advance

This is the macro with the adjustments I made:
Private Sub Worksheet_Activate()
Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, Range("V:V")) Is Nothing Or Target.Row < 5 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim prod As Range, desWS1 As Worksheet, desWS2 As Worksheet
Set desWS1 = Sheets("PRECIOS PRODUCTOS Y SERVICIOS")
Set desWS2 = Sheets("PUNTO DE EQUILIBRIO")
With desWS2
.Activate
.Cells(.Rows.Count, "A").End(xlUp).Offset(1) = Target.Offset(, -21)
.Cells(.Rows.Count, "A").End(xlUp).Offset(, 1).Select
End With
Set prod = desWS1.Range("A:A").Find(Target.Offset(, -21), LookIn:=xlValues, lookat:=xlWhole)
If Not prod Is Nothing Then
With desWS1
.Activate
.Range("D" & prod.Row) = Target.Offset(, 1)
.Range("E" & prod.Row).Select
End With
Else
With desWS1
.Activate
.Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 4).Value = Array(Target.Offset(, -21), Target.Offset(, -20), Target.Offset(, -19), Target.Offset(, 1))
.Cells(.Rows.Count, "C").End(xlUp).Offset(, 2).Select
End With
End If
MsgBox ("SE HA ENVIADO AL MODULO PRECIOS PRODUCTOS Y SERVICIOS, LA INFORMACIÓN DE ESTE PRODUCTO O SERVICIO." & vbCr & Chr(13) & _
"POR FAVOR COMPLETE LA INFORMACIÓN SOLICITADA" & Chr(13) & _
"!OPERACIÓN REALIZADA SATISFACTORIAMENTE¡"), vbInformation, "CALCULADORA DE PRECIOS Y COSTOS DATCHEL PLU"
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Translated with DeepL.com (free version)

 

[leobrice]

Hi @mumps how are you.
Mumps can you help me with this case. It's very similar to the one that you resolved.
Thanks