Data validation
 

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,

"mac" wrote:

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.
--
thank you mac

Data validation
 

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.

--
thank you mac


"Dave Ramage" wrote:

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,

"mac" wrote:

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.
--
thank you mac

Data validation
 

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

---
Regards,
Tom Ogilvy

"mac" wrote:

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.

--
thank you mac


"Dave Ramage" wrote:

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,

"mac" wrote:

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.
--
thank you mac

Data validation
 

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


"Tom Ogilvy" wrote:

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

---
Regards,
Tom Ogilvy

"mac" wrote:

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.

--
thank you mac


"Dave Ramage" wrote:

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,

"mac" wrote:

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.
--
thank you mac

Data validation
 

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)


"mac" wrote in message
...
I am getting an error "Compile error: Invalid outside procedure. Thamk
you
for the time to help me.
--
thank you mac


"Tom Ogilvy" wrote:

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

---
Regards,
Tom Ogilvy

"mac" wrote:

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.

--
thank you mac


"Dave Ramage" wrote:

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,

"mac" wrote:

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.
--
thank you mac

Data validation
 

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.
--
thank you mac


"Chip Pearson" wrote:

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)


"mac" wrote in message
...
I am getting an error "Compile error: Invalid outside procedure. Thamk
you
for the time to help me.
--
thank you mac


"Tom Ogilvy" wrote:

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

---
Regards,
Tom Ogilvy

"mac" wrote:

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.

--
thank you mac


"Dave Ramage" wrote:

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,

"mac" wrote:

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.
--
thank you mac

Data validation
 

post current code...

--
Tim Williams
Palo Alto, CA


"mac" wrote in message ...
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.
--
thank you mac


"Chip Pearson" wrote:

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)


"mac" wrote in message
...
I am getting an error "Compile error: Invalid outside procedure. Thamk
you
for the time to help me.
--
thank you mac


"Tom Ogilvy" wrote:

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

---
Regards,
Tom Ogilvy

"mac" wrote:

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.

--
thank you mac


"Dave Ramage" wrote:

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,

"mac" wrote:

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.
--
thank you mac

Data validation
 

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
--
thank you mac


"Tim Williams" wrote:

post current code...

--
Tim Williams
Palo Alto, CA


"mac" wrote in message ...
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.
--
thank you mac


"Chip Pearson" wrote:

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)


"mac" wrote in message
...
I am getting an error "Compile error: Invalid outside procedure. Thamk
you
for the time to help me.
--
thank you mac


"Tom Ogilvy" wrote:

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

---
Regards,
Tom Ogilvy

"mac" wrote:

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.

--
thank you mac


"Dave Ramage" wrote:

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,

"mac" wrote:

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.
--
thank you mac

Data validation
 

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

mac wrote:

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
--
thank you mac

"Tim Williams" wrote:

post current code...

--
Tim Williams
Palo Alto, CA


"mac" wrote in message ...
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.
--
thank you mac


"Chip Pearson" wrote:

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)


"mac" wrote in message
...
I am getting an error "Compile error: Invalid outside procedure. Thamk
you
for the time to help me.
--
thank you mac


"Tom Ogilvy" wrote:

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

---
Regards,
Tom Ogilvy

"mac" wrote:

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.

--
thank you mac


"Dave Ramage" wrote:

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,

"mac" wrote:

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.
--
thank you mac







--

Dave Peterson

Data validation
 

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.
--
thank you mac


"Dave Peterson" wrote:

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

mac wrote:

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
--
thank you mac

"Tim Williams" wrote:

post current code...

--
Tim Williams
Palo Alto, CA


"mac" wrote in message ...
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.
--
thank you mac


"Chip Pearson" wrote:

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)


"mac" wrote in message
...
I am getting an error "Compile error: Invalid outside procedure. Thamk
you
for the time to help me.
--
thank you mac


"Tom Ogilvy" wrote:

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

---
Regards,
Tom Ogilvy

"mac" wrote:

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.

--
thank you mac


"Dave Ramage" wrote:

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,

"mac" wrote:

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.
--
thank you mac







--

Dave Peterson