urgent-looping through data validation list then printing as per each value

[kidkarma]

Hey all,

I feel like this is a simple issue but am trying to get my head around
using VBA.

I need a code that loops thorugh a data validation list and as it
selects each value in the list, prints out the active worksheet.
(guessing some sort of loop is needed)sorry for sounding like such a
beginner.

thanks!

 

[Chip Pearson]

The following code should get you started. It assumes that
Worksheets(1).Range("A1") has a List type validation with the list pointing
to a range of cells. There is no error check to ensure that this is true.

Sub AAA()
Dim DV As Validation
Dim S As String
Dim DVRng As Range
Dim R As Range

Set DV = Worksheets(1).Range("A1").Validation
S = DV.Formula1
S = Mid(S, 2)
Set DVRng = DV.Parent.Worksheet.Range(S)
For Each R In DVRng
DV.Parent.Value = R.Value
DV.Parent.Worksheet.PrintOut
Next R
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Tom Ogilvy]

If the data validation list is from a range on the sheet and you don't know
what it is (if you do, just loop through that).

In each case, assume I5 is the cell with the data validation:

Sub LoopListRange()
Dim as String, rng as Range, cell as Range
s = Evaluate(Activesheet.Range("I5").Validation.Formula1)
set rng = Activesheet.Range(s)
for each cell in rng
Activesheet.Range("I5").Value = cell.Value
Activesheet.Printout
Next
End Sub

If the list is hand entered such as John,Jack,Jill,Mary

Sub LoopListArray()
Dim s as Variant, i as Long
s = Split(Activesheet.Range("I5").Validation.Formula1,",")
for i = lbound(s) to ubound(s)
ActiveSheet.Range("I5").Value = s(i)
ActiveSheet.Printout
Next
End Sub

 

[Tom Ogilvy]

correction on the first one

Sub LoopListRange()
Dim rng as Range, cell as Range
Set rng = Evaluate(Activesheet.Range("I5").Validation.Formula1)
for each cell in rng
Activesheet.Range("I5").Value = cell.Value
Activesheet.Printout
Next
End Sub