Macro to cycle through drop-down selection (with conditional) to print to PDF (name in cell) and save in X path (path in cell)

E

elphonshevax

New Member
Joined
Apr 29, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Goodnight everyone,

First I would like to tell you what I have been doing so far and then request your help.

I have this excel file which is mainly made up of two sheets:
1. Apartment information sheet
2. Sheet to make quotes for the apartments

In the quotation sheet, I have a cell where I write the nomenclature of the apartment (D2), I specify if it has parking (H2) and deposit (I2), and the quotation data is automatically completed (I bring the data from the sheet of information). At this point I could print a PDF and have the quote. However, I must emphasize that there are 5 types of apartments and each type has different customization options, for example, the type 1 apartment has 11 customization options ranging from 1 to 11, and on the other hand, the type 3 apartment has 4 customization options ranging from "STD" to 3 (The type of apartment brings it in cell E27). In that order of ideas, when I am printing the quotes, in a cell I have a drop-down with the customization options (E28), I choose which option I am going to print and I print it. So, for example, for the case of apartment type 1, I have to print 11 PDFs.

I have tried to streamline this activity with the help of macros, for which I found this

Sub Print_All_To_PDF ()
Dim strValidationRange As String
Dim rngValidation As Range
Dim rngDepartment As Range

'Turn off screen updating
Application.ScreenUpdating = False

'Identify the source list of the data validation
strValidationRange = Range ("E28"). Validation.Formula1
Set rngValidation = Range (strValidationRange)

'Set the value in the selection cell to each selection in turn
'and print the results.
For Each rngDepartment In rngValidation.Cells
Range ("E28"). Value = rngDepartment.Value
ActiveSheet.ExportAsFixedFormat Type: = xlTypePDF, Filename: = ThisWorkbook.Path & "\" & Range ("L18"). Value _
, Quality: = xlQualityStandard, IncludeDocProperties: = True, IgnorePrintAreas _
: = False, OpenAfterPublish: = False
Next
'Turn screen updating back on
Application.ScreenUpdating = True
End Sub

This allows me to print the 12 customization options (1 to 11 + STD) for the apartments and save the PDFs with the name I assign to the file (L18) in the folder where I have the file. It has been a considerable improvement.

I want to go a little further and I would like you to please help me.
Currently I have another drop-down box in a box that validates the options available for each type of apartment (L16), that is, if it is a type 1 it allows me to choose only from 1 to 11 and if it is a type 3 it allows me to choose only from STD to 3 (I have this configured in another sheet called Dropdowns).
The idea is that the macro cycle in that drop-down menu that shows only the options for X type of apartment (L16) and that way only the corresponding options are printed. Also, I have in another cell the name of the path where I want the file to be saved (L19), so as not to have to manually move the PDFs or move the file and have them printed elsewhere.

Vinculacion clientes TEST.xlsm
ABCDEFGHIJKLM
1
2B41CDISPONIBLECON PARQUEADEROCON DEPÓSITO
3
4
5
6
7
8
9
10
11
12
13
14
15
161
17Casa del LagoApartamento 1C - tipo 1T1 (B4 - 1C) V.NORTE-CAMPESTRE
18Vista norte, campestreOpción 11 - Gran Salón + 3 Alcobas + Alcoba ServicioT1 (B4 - 1C) V.NORTE-CAMPESTRE - O11 CON P7 + D3
19D:\Escritorio\Macros\Test PDF\T1 (B4 - 1C) V.NORTE-CAMPESTRE
20INFORMACIÓN DEL INMUEBLEFORMA DE PAGO
21ApartamentoB4 - 1CCUOTA #VALORB4
22Área Apartamento (m2)114,95Separación$ 5.000.000C8
23Área Terraza (m2)6,271$ 6.020.000
24Área Jardín (m2)1122$ 6.020.000STD
25*Parquadero No. (opcional)P73$ 6.020.0001
26*Depósito No. (opcional)D34$ 6.020.0002
27Tipo de ApartamentoTIPO 15$ 6.020.0003
28*Opción (opcional)116$ 15.000.0004
29INVERSIÓN*7$ 6.020.0005
30Valor Apto$ 580.000.0008$ 6.020.0006
31Valor Parqueadero$ 18.000.0009$ 6.020.0007
32Valor Depósito$ 7.500.00010$ 6.020.0008
33Valor Opción$ 12.500.00011$ 6.020.0009
34Subtotal$ 605.500.00012$ 15.000.00010
35Valor Total$ 618.000.00013$ 6.020.00011
36FINANCIAMIENTO PAGO ESTÁNDAR14$ 6.020.000
37Cuota Inicial30%$ 185.400.00015$ 6.020.000CON PARQUEADERO
38Separación$ 5.000.00016$ 6.020.000SIN PARQUEADERO
39Saldo$ 180.400.00017$ 6.020.000
40Cuotas mensuales20$ 6.020.00018$ 15.000.000CON DEPÓSITO
41Cuotas Extraordinarias4$ 15.000.00019$ 6.020.000SIN DEPÓSITO
42Saldo a la Entrega o Crédito70%$ 432.600.00020$ 6.020.000
4321$ 6.020.000C8 PISO 1
44UBICACIÓN Y VISTA INMUEBLE22$ 6.020.000C8 PISO 2
4523$ 6.020.000C8 PISO 3
4624$ 15.000.000C8 PISO 4$ 185.400.000
47Saldo contraentrega$ 432.600.000B4 PISO 1
48B4 PISO 2
49OBSERVACIONESB4 PISO 3
50B4 PISO 4
51*Los precios aquí descritos pueden variar sin previo aviso.
52*Esta cotización tiene validez por treinta (30) días.
53
54
55
56
57
58
59
60
61
62
63
64
65DESCRIPCIÓN INMUEBLE
66
67
68APARTAMENTO TIPO 1APARTAMENTO TIPO 1 + OPCIÓN 11 - Gran Salón + 3 Alcobas + Alcoba Servicio
69OPCIÓN 11 - Gran Salón + 3 Alcobas + Alcoba Servicio
70
71Área Total: 114,95 M2
72Área Jardín: 112 M2
73
74• Gran Salón - Comedor + Terraza • Cocina Integral Cerrada • Cuarto de Ropas • 3 Alcobas • Baño Social • 3 Baños • Alcoba Servicio
75
76*Todos los apartamentos con Parqueadero Opcional.
77*Todos los apartamentos con Depósito Opcional en el mismo piso.
78*Planta eléctrica de cobertura total.
79*Apartamentos en primer piso con posibilidad de construcción de piscina privada.
80
81
82
83
84
85
86
87
88
89
90
91Z
92
93
Cotización
Cell Formulas
RangeFormula
E2E2=+UPPER(IF($C$2="B4",VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$5:$U$36,3,FALSE),VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$37:$U$67,3,FALSE)))
C17C17=+IF(C2="B4","Casa del Lago",IF(C2="C8","Casa de Campo",))
C18C18=+CONCATENATE("Vista ",LOWER(IF($C$2="B4",VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$5:$U$36,12,FALSE),VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$37:$U$67,12,FALSE))))
H17H17=+CONCATENATE("Apartamento ",D2," - ",LOWER(E27))
H18H18=+CONCATENATE("Opción ",IF(E28="STD","Estándar",E28)," - ",IF(E27="TIPO 1",VLOOKUP(E28,Opciones!C14:H24,5,FALSE),IF(E27="TIPO 2",VLOOKUP(E28,Opciones!C26:H36,5,FALSE),IF(E27="TIPO 3",VLOOKUP(E28,Opciones!C38:H41,5,FALSE),IF(E27="TIPO 4",VLOOKUP(E28,Opciones!C43:H43,5,FALSE),IF(E27="TIPO 5",VLOOKUP(E28,Opciones!C45:H46,5,FALSE),IF(E27="TIPO 6",VLOOKUP(E28,Opciones!C48:H49,5,FALSE))))))))
L17L17=CONCATENATE((CHOOSE(LEN(E27) - LEN(SUBSTITUTE(E27," ","")) + 1,LEFT(E27),LEFT(E27, 1) & MID(E27, FIND(" ", E27) + 1, 1),LEFT(E27, 1) & MID(E27, FIND(" ", E27) + 1, 1) & MID(E27, FIND(" ", E27, FIND(" ", E27) + 1) + 1, 1)))," (",E21,") ",LEFT(C18,1),".",LEFT(IF($C$2="B4",VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$5:$U$36,12,FALSE),VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$37:$U$67,12,FALSE)),(FIND(",",IF($C$2="B4",VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$5:$U$36,12,FALSE),VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$37:$U$67,12,FALSE)),1)-1)),"-",UPPER(MID(C18,FIND(" ",C18,FIND(" ",C18)+1)+1,256)))
L18L18=CONCATENATE((CHOOSE(LEN(E27) - LEN(SUBSTITUTE(E27," ","")) + 1,LEFT(E27),LEFT(E27, 1) & MID(E27, FIND(" ", E27) + 1, 1),LEFT(E27, 1) & MID(E27, FIND(" ", E27) + 1, 1) & MID(E27, FIND(" ", E27, FIND(" ", E27) + 1) + 1, 1)))," (",E21,") ",LEFT(C18,1),".",LEFT(IF($C$2="B4",VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$5:$U$36,12,FALSE),VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$37:$U$67,12,FALSE)),(FIND(",",IF($C$2="B4",VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$5:$U$36,12,FALSE),VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$37:$U$67,12,FALSE)),1)-1)),"-",UPPER(MID(C18,FIND(" ",C18,FIND(" ",C18)+1)+1,256))," - O",E28," CON ",E25," + ",E26)
L19L19=+CONCATENATE("D:\Escritorio\Macros\Test PDF","\",L17)
E21E21=+CONCATENATE(UPPER(C2)," - ",UPPER(D2))
E22E22=+IF($C$2="B4",VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$5:$U$36,7,FALSE),VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$37:$U$67,7,FALSE))
E23E23=+IF($C$2="B4",VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$5:$U$36,5,FALSE),VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$37:$U$67,5,FALSE))
E24E24=+IF($C$2="B4",VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$5:$U$36,6,FALSE),VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$37:$U$67,6,FALSE))
E25E25=+IF(H2="CON PARQUEADERO",IF($C$2="B4",VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$5:$U$36,18,FALSE),VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$37:$U$67,18,FALSE)),IF(H2="SIN PARQUEADERO","",))
E26E26=+IF(I2="CON DEPÓSITO",IF($C$2="B4",VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$5:$U$36,14,FALSE),VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$37:$U$67,14,FALSE)),IF(I2="SIN DEPÓSITO","",))
E27E27=+IF($C$2="B4",VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$5:$U$36,2,FALSE),VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$37:$U$67,2,FALSE))
E30E30=+IF($C$2="B4",VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$5:$U$36,19,FALSE),VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$37:$U$67,19,FALSE))
E31E31=+IF(H2="CON PARQUEADERO",IF($C$2="B4",VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$5:$U$36,17,FALSE),VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$37:$U$67,17,FALSE)),IF(H2="SIN PARQUEADERO",0,))
E32E32=+IF(I2="CON DEPÓSITO",IF($C$2="B4",VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$5:$U$36,13,FALSE),VLOOKUP(CONCATENATE($C$2," - ",$D$2),'ListaDePrecios CLC'!$B$37:$U$67,13,FALSE)),IF(I2="SIN DEPÓSITO",0,))
E33E33=+IF(E27="TIPO 1",VLOOKUP(E28,Opciones!C14:E24,3,FALSE),IF(E27="TIPO 2",VLOOKUP(E28,Opciones!C26:E36,3,FALSE),IF(E27="TIPO 3",VLOOKUP(E28,Opciones!C38:E41,3,FALSE),IF(E27="TIPO 4",VLOOKUP(E28,Opciones!C43:E43,3,FALSE),IF(E27="TIPO 5",VLOOKUP(E28,Opciones!C45:E46,3,FALSE),IF(E27="TIPO 6",VLOOKUP(E28,Opciones!C48:E49,3,FALSE)))))))
E34E34=+SUM(E30:E32)
E35E35=+E34+E33
E37E37=D37*E35
E39E39=+E37-E38
E40E40=+(E39-(E41*D41))/D40
E41E41=+IF(E27="TIPO 1",NUMBERVALUE("$ 15.000.000"),IF(E27="TIPO 2",NUMBERVALUE("$ 12.900.000"),IF(E27="TIPO 3",NUMBERVALUE("$ 12.000.000"),IF(E27="TIPO 4",NUMBERVALUE("$ 8.000.000"),IF(E27="TIPO 5",NUMBERVALUE("$ 5.500.000"))))))
D42D42=100%-D37
E42E42=+E35-E37
M46M46=+SUM(I22:I46)
I22I22=+$E$38
I23:I27,I29:I33,I35:I39,I41:I45I23=+$E$40
I28,I34,I40,I46I28=+$E$41
I47I47=+E42
H68H68=+CONCATENATE("APARTAMENTO ",E27)
L68L68=+CONCATENATE(H68," + ",H69)
H69H69=+CONCATENATE("OPCIÓN ",E28," - ",IF(E27="TIPO 1",VLOOKUP(E28,Opciones!C14:H24,5,FALSE),IF(E27="TIPO 2",VLOOKUP(E28,Opciones!C26:H36,5,FALSE),IF(E27="TIPO 3",VLOOKUP(E28,Opciones!C38:H41,5,FALSE),IF(E27="TIPO 4",VLOOKUP(E28,Opciones!C43:H43,5,FALSE),IF(E27="TIPO 5",VLOOKUP(E28,Opciones!C45:H46,5,FALSE),IF(E27="TIPO 6",VLOOKUP(E28,Opciones!C48:H49,5,FALSE))))))))
H71H71=+CONCATENATE("Área Total: ",ROUNDUP(E22,2)," M2")
H72H72=+IF(E24=0,"",CONCATENATE("Área Jardín: ",ROUNDUP(E24,0)," M2"))
H74H74=+IF(E27="TIPO 1",VLOOKUP(E28,Opciones!C14:H24,6,FALSE),IF(E27="TIPO 2",VLOOKUP(E28,Opciones!C26:H36,6,FALSE),IF(E27="TIPO 3",VLOOKUP(E28,Opciones!C38:H41,6,FALSE),IF(E27="TIPO 4",VLOOKUP(E28,Opciones!C43:H43,6,FALSE),IF(E27="TIPO 5",VLOOKUP(E28,Opciones!C45:H46,6,FALSE),IF(E27="TIPO 6",VLOOKUP(E28,Opciones!C48:H49,6,FALSE)))))))
H79H79=+IF(E27="TIPO 1","*Apartamentos en primer piso con posibilidad de construcción de piscina privada.","")
Cells with Data Validation
CellAllowCriteria
C2:C3List=$L$21:$L$22
E28List=$L$24:$L$35
H2List=$L$37:$L$38
I2List=$L$40:$L$41
L16List=INDIRECTO(SUSTITUIR(E27;" ";"_"))
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
You must log in or register to reply here.

Similar threads

T
  • Question
VBA Macro running, but not saving to Sharepoint
Replies
3
Views
553
Fluff
Fluff
J
  • Question
Take Excel Worksheet, Divide into Worksheets/Workbooks by Employee/Card #, Email via Gmail to Employee for Input
Replies
9
Views
592
jennifer7i884
J
C
  • Solved
VBA macro to search each sheet cell for specific text and print to PDF when true
Replies
6
Views
574
camerong
C
K
  • Solved
Inserting partial column VBA
Replies
3
Views
492
keef2
K
1
  • Question
Auto filter column on vlookup update on every sheets of the workbook
Replies
1
Views
169
10ml
1

Forum statistics

Threads
1,214,647
Messages
6,120,722
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top