Forum Discussion
Excel
Print by adding a range argument to the PrintOut method
You can use a loop to iterate through the items in the drop-down list and print the selected range for each item.
Here’s an example that assumes the drop-down list is a data validation list in cell A1 on Sheet1 and the range to print is B2:C3 on Sheet1.
Sub PrintDropDownList()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim val As Variant
Set ws = Worksheets("Sheet1")
Set rng = ws.Range("A1").Validation.Formula1
rng = Right(rng, Len(rng) - 1)
Set rng = ws.Range(rng)
For Each cell In rng.Cells
val = cell.Value
ws.Range("A1").Value = val
ws.Range("B2:C3").PrintOut
Next cell
End SubThis code sets the value of cell A1 to each item in the drop-down list and then prints the range B2:C3.
You can adjust the worksheet and range references to match your specific needs.
I hope this helps!
- I tried to run the code, but it responded with an error "type mismatch". The phrase ".Formula1" was also highlighted.
Here is an example with step by step instracition (see the file too):
Create a named range for the cells that contain the drop-down list options. To do this, select the cells that contain the options and then click on the “Formulas” tab. In the “Defined Names” group, click on “Define Name” and enter a name for the range.
Create a drop-down list in a cell by going to the “Data” tab and clicking on “Data Validation” in the “Data Tools” group. In the “Data Validation” dialog box, select “List” in the “Allow” drop-down list. In the “Source” field, enter the name of the named range that you created in step 1.
Create a VBA macro that uses the value of the cell with the drop-down list to determine which print area to use. Here is an example of such a macro:
Sub PrintSelectedRange()
'Get the value of the cell with the drop-down list
Dim selectedOption As String
selectedOption = Range("A1").Value
'Select the print area based on the selected option
If selectedOption = "Examination" Then
Range("C9:R86").Select
ElseIf selectedOption = "score" Then
Range("W4:AB46").Select
End If
'Set the print area to the selected range
With ActiveSheet.PageSetup
.PrintArea = Selection.Address
End With
'Print the selected range
ActiveSheet.PrintOut
End SubThis macro gets the value of the cell with the drop-down list (in this example, cell A1) and uses it to determine which range of cells to select as the print area. You can modify this code to match your specific needs.
I hope this helps!