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
- '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.