Data validation

[Guest]

Hi,
I have a excel file that I set up with data validation. When I select the
list the data changes. Can anyone help me set up a macro to select each item
on list and then print it? Any help will be greatly appreciated.

 

[Guest]

mac,

This should work for you. Make sure the worksheet in question is active
before you run it, and change the cell reference "A1" to the appropriate
value.

Sub Cycle_ValidationValues()
Dim rngTarget As Range, rngList As Range, rngR As Range
Dim strList As String, strInitialValue As String

'set reference to target cell
Set rngTarget = Sheets("Sheet1").Range("A1") 'or whatever
'remeber initial value
strInitialValue = rngTarget.Value
'get the source list range
strList = rngTarget.Validation.Formula1
'remove the "=" from the start
strList = Right(strList, Len(strList) - 1)
'set reference to this list range
Set rngList = Range(strList)

'loop through each cell in list range
For Each rngR In rngList.Cells
rngTarget.Value = rngR.Value
ActiveSheet.PrintOut 'print with default settings
Next rngR

'reset initial value
rngTarget.Value = strInitialValue
End Sub

Cheers,
Dave,

 

[Guest]

Hi Dave,
Thank you for your help. I really appreciated it. I am not sure I explained
it. My list is on the same sheet it goes from Q3:q431. I would like to have
it lookup for first row(q3) paste it into d1 and then print it. Then go to
the next row Q4 and do the same up to row 431. Is this possible to do?
Again thank you for taking to time to help.

 

[Guest]

something like this for example.

Dim cell as Range
for each cell in Range("Q3:Q431")
Range("D1").Value = cell.Value
' change next line to reflect the range to printout.
Range("A1:M44").Printout
Next

 

[Guest]

I am getting an error "Compile error: Invalid outside procedure. Thamk you
for the time to help me.

 

[Chip Pearson]

You need to put Tom's code within a procedure. E.g.,

Sub AAA()
' Tom's code
End Sub


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

 

[Guest]

Hello,
I really feel stupid. I have done the sub & end Sub, now i get the message
"compile error: for without next". As ypu can tell I am a novice at macros,
so any help you give me is greatly appreciated.

 

[Tim Williams]

post current code...

 

[Guest]

Here it is. Thank you

Sub Sheet2()
Dim cell As Range
For Each cell In Range("Q3:Q431")
Range("D1").Value = cell.Value
' change next line to reflect the range to printout.
Range("b3:e18").PrintOut
End Sub

 

[Dave Peterson]

Sub PrintSheet2()
Dim cell As Range
For Each cell In Range("Q3:Q431")
Range("D1").Value = cell.Value
' change next line to reflect the range to printout.
Range("b3:e18").PrintOut
Next Cell '<-- added
End Sub

 

[Guest]

Sorry so long to get back, was sick. I just want to thank you all for the
help you gave me. I works like a charm. Again thank you so much.