Helo

I think the Subject says it all. I'm putting values into a series of cells in a
column, about 10 different rows, and there are cells down below in that same
column of the same sheet that are USUALLY the same value. Rather than having to
re-type them again, I'd like to have them copied to the target cells as I type
them in the first group of cells, but I still want to be able to override
(over-write) the values that were copied to the target cells, as they are not
ALWAYS the same.

Thanks

Harry Fine

Private Sub Worksheet_Change(ByVal Target As Range)
if target.count 1 then exit sub
if target.column = 3 then
application.EnableEvents = False
target.Resize(10,1).Value = Target.Value
application.EnableEvents = True
End if
End Sub

Change the 10 to the number of row you want to fill. Change 3 to reflect
the column where you want this behavior.

Right click on the sheet tab of this worksheet and select view code. Paste
in code similar to the above.

another approach is to select all the cells to be filled, go to the formula
bar and enter the data, then do Ctrl+Enter rather than just enter.

--
regards,
Tom Ogilvy

"Harry" wrote in message
.. .
Helo

I think the Subject says it all. I'm putting values into a series of
cells in a
column, about 10 different rows, and there are cells down below in that
same
column of the same sheet that are USUALLY the same value. Rather than
having to
re-type them again, I'd like to have them copied to the target cells as I
type
them in the first group of cells, but I still want to be able to override
(over-write) the values that were copied to the target cells, as they are
not
ALWAYS the same.

Thanks

Harry Fine

Tom, this is great, and I am greatful, but....where do I put this routine? Is
it a Visual Basic script, or a Macro. Where do I start.

Harry


On Mon, 14 Feb 2005 10:38:37 -0500, "Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
if target.count 1 then exit sub
if target.column = 3 then
application.EnableEvents = False
target.Resize(10,1).Value = Target.Value
application.EnableEvents = True
End if
End Sub

Change the 10 to the number of row you want to fill. Change 3 to reflect
the column where you want this behavior.

Right click on the sheet tab of this worksheet and select view code. Paste
in code similar to the above.

another approach is to select all the cells to be filled, go to the formula
bar and enter the data, then do Ctrl+Enter rather than just enter.

--
regards,
Tom Ogilvy

"Harry" wrote in message
. ..
Helo

I think the Subject says it all. I'm putting values into a series of
cells in a
column, about 10 different rows, and there are cells down below in that
same
column of the same sheet that are USUALLY the same value. Rather than
having to
re-type them again, I'd like to have them copied to the target cells as I
type
them in the first group of cells, but I still want to be able to override
(over-write) the values that were copied to the target cells, as they are
not
ALWAYS the same.

Thanks

Harry Fine

As I said:

Right click on the sheet tab of this worksheet and select view code. Paste
in code similar to the above.

See Chip Pearson's page on Events
http://www.cpearson.com/excel/events.htm

for tutorials/ getting started with macros:


David McRitchie's site


http://www.mvps.org/dmcritchie/excel....htm#tutorials


the vba tutorials are below the excel tutorials.


This has good info as well (for Excel)



http://msdn.microsoft.com/office/und.../odc_super.asp

http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Regards,
Tom Ogilvy


"Harry" wrote in message
.. .
Tom, this is great, and I am greatful, but....where do I put this routine?
Is
it a Visual Basic script, or a Macro. Where do I start.

Harry


On Mon, 14 Feb 2005 10:38:37 -0500, "Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
if target.count 1 then exit sub
if target.column = 3 then
application.EnableEvents = False
target.Resize(10,1).Value = Target.Value
application.EnableEvents = True
End if
End Sub

Change the 10 to the number of row you want to fill. Change 3 to reflect
the column where you want this behavior.

Right click on the sheet tab of this worksheet and select view code.
Paste
in code similar to the above.

another approach is to select all the cells to be filled, go to the
formula
bar and enter the data, then do Ctrl+Enter rather than just enter.

--
regards,
Tom Ogilvy

"Harry" wrote in message
. ..
Helo

I think the Subject says it all. I'm putting values into a series of
cells in a
column, about 10 different rows, and there are cells down below in that
same
column of the same sheet that are USUALLY the same value. Rather than
having to
re-type them again, I'd like to have them copied to the target cells as
I
type
them in the first group of cells, but I still want to be able to
override
(over-write) the values that were copied to the target cells, as they
are
not
ALWAYS the same.

Thanks

Harry Fine

Sorry Tom, I missed that. But it can't possibly work. It will need to know the
starting cell and range to grab it from (Column K, rows 16-22) and then where to
start copying the data (Column K, rows 48 - 54)

Harry


On Mon, 14 Feb 2005 11:53:03 -0500, "Tom Ogilvy" wrote:

As I said:

Right click on the sheet tab of this worksheet and select view code. Paste
in code similar to the above.

See Chip Pearson's page on Events
http://www.cpearson.com/excel/events.htm

for tutorials/ getting started with macros:


David McRitchie's site


http://www.mvps.org/dmcritchie/excel....htm#tutorials


the vba tutorials are below the excel tutorials.


This has good info as well (for Excel)



http://msdn.microsoft.com/office/und.../odc_super.asp

http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Regards,
Tom Ogilvy


"Harry" wrote in message
. ..
Tom, this is great, and I am greatful, but....where do I put this routine?
Is
it a Visual Basic script, or a Macro. Where do I start.

Harry


On Mon, 14 Feb 2005 10:38:37 -0500, "Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
if target.count 1 then exit sub
if target.column = 3 then
application.EnableEvents = False
target.Resize(10,1).Value = Target.Value
application.EnableEvents = True
End if
End Sub

Change the 10 to the number of row you want to fill. Change 3 to reflect
the column where you want this behavior.

Right click on the sheet tab of this worksheet and select view code.
Paste
in code similar to the above.

another approach is to select all the cells to be filled, go to the
formula
bar and enter the data, then do Ctrl+Enter rather than just enter.

--
regards,
Tom Ogilvy

"Harry" wrote in message
. ..
Helo

I think the Subject says it all. I'm putting values into a series of
cells in a
column, about 10 different rows, and there are cells down below in that
same
column of the same sheet that are USUALLY the same value. Rather than
having to
re-type them again, I'd like to have them copied to the target cells as
I
type
them in the first group of cells, but I still want to be able to
override
(over-write) the values that were copied to the target cells, as they
are
not
ALWAYS the same.

Thanks

Harry Fine

That's correct Harry - but it works for the way I interpreted your
description. Have you ever heard the story of the 3 blind men trying to
describe an elephant.

so now, based on what you have told us so far

Private Sub Worksheet_Change(ByVal Target As Range)
if target.columns.count 1 then exit sub
if target.column < 11 then exit sub
if Target(1).Row = 16 and Target(1).Row <= 22 then
application.EnableEvents = False
me.Range("K48:K54").Value = _
me.Range("K16:K22").Value
application.EnableEvents = True
End if
End Sub

--
Regards,
Tom Ogilvy


"Harry" wrote in message
.. .
Sorry Tom, I missed that. But it can't possibly work. It will need to
know the
starting cell and range to grab it from (Column K, rows 16-22) and then
where to
start copying the data (Column K, rows 48 - 54)

Harry


On Mon, 14 Feb 2005 11:53:03 -0500, "Tom Ogilvy" wrote:

As I said:

Right click on the sheet tab of this worksheet and select view code.
Paste
in code similar to the above.

See Chip Pearson's page on Events
http://www.cpearson.com/excel/events.htm

for tutorials/ getting started with macros:


David McRitchie's site


http://www.mvps.org/dmcritchie/excel....htm#tutorials


the vba tutorials are below the excel tutorials.


This has good info as well (for Excel)




http://msdn.microsoft.com/office/und...tarted/default
..aspx?pull=/library/en-us/odc_xl2003_ta/html/odc_super.asp

http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Regards,
Tom Ogilvy


"Harry" wrote in message
. ..
Tom, this is great, and I am greatful, but....where do I put this
routine?
Is
it a Visual Basic script, or a Macro. Where do I start.

Harry


On Mon, 14 Feb 2005 10:38:37 -0500, "Tom Ogilvy"
wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
if target.count 1 then exit sub
if target.column = 3 then
application.EnableEvents = False
target.Resize(10,1).Value = Target.Value
application.EnableEvents = True
End if
End Sub

Change the 10 to the number of row you want to fill. Change 3 to
reflect
the column where you want this behavior.

Right click on the sheet tab of this worksheet and select view code.
Paste
in code similar to the above.

another approach is to select all the cells to be filled, go to the
formula
bar and enter the data, then do Ctrl+Enter rather than just enter.

--
regards,
Tom Ogilvy

"Harry" wrote in message
. ..
Helo

I think the Subject says it all. I'm putting values into a series
of
cells in a
column, about 10 different rows, and there are cells down below in
that
same
column of the same sheet that are USUALLY the same value. Rather
than
having to
re-type them again, I'd like to have them copied to the target cells
as
I
type
them in the first group of cells, but I still want to be able to
override
(over-write) the values that were copied to the target cells, as
they
are
not
ALWAYS the same.

Thanks

Harry Fine

That was pretty cool. It works great. Tom, do you do this for a living, and
what movitvates you to spend time writing stuff for new people like me.

May I impose on you one more time. Unfortunately, there is not a 1:1
relationship between the data in the k16-22 cells, and the data in the k48-54
cells. There are two fields in the former, cells K16 & 17, Street # and Street
Name, which are concatenated together below as Street Address in K48. Is there
any way in the script you gave me to do something like:

me.Range("K48:K54").Value = _
me.Range("K16&17:K22").Value

Thanks

Harry


On Mon, 14 Feb 2005 13:55:52 -0500, "Tom Ogilvy" wrote:

That's correct Harry - but it works for the way I interpreted your
description. Have you ever heard the story of the 3 blind men trying to
describe an elephant.

so now, based on what you have told us so far

Private Sub Worksheet_Change(ByVal Target As Range)
if target.columns.count 1 then exit sub
if target.column < 11 then exit sub
if Target(1).Row = 16 and Target(1).Row <= 22 then
application.EnableEvents = False
me.Range("K48:K54").Value = _
me.Range("K16:K22").Value
application.EnableEvents = True
End if
End Sub

--
Regards,
Tom Ogilvy


"Harry" wrote in message
. ..
Sorry Tom, I missed that. But it can't possibly work. It will need to
know the
starting cell and range to grab it from (Column K, rows 16-22) and then
where to
start copying the data (Column K, rows 48 - 54)

Harry


On Mon, 14 Feb 2005 11:53:03 -0500, "Tom Ogilvy" wrote:

As I said:

Right click on the sheet tab of this worksheet and select view code.
Paste
in code similar to the above.

See Chip Pearson's page on Events
http://www.cpearson.com/excel/events.htm

for tutorials/ getting started with macros:


David McRitchie's site


http://www.mvps.org/dmcritchie/excel....htm#tutorials


the vba tutorials are below the excel tutorials.


This has good info as well (for Excel)




http://msdn.microsoft.com/office/und...tarted/default
.aspx?pull=/library/en-us/odc_xl2003_ta/html/odc_super.asp

http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Regards,
Tom Ogilvy


"Harry" wrote in message
. ..
Tom, this is great, and I am greatful, but....where do I put this
routine?
Is
it a Visual Basic script, or a Macro. Where do I start.

Harry


On Mon, 14 Feb 2005 10:38:37 -0500, "Tom Ogilvy"
wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
if target.count 1 then exit sub
if target.column = 3 then
application.EnableEvents = False
target.Resize(10,1).Value = Target.Value
application.EnableEvents = True
End if
End Sub

Change the 10 to the number of row you want to fill. Change 3 to
reflect
the column where you want this behavior.

Right click on the sheet tab of this worksheet and select view code.
Paste
in code similar to the above.

another approach is to select all the cells to be filled, go to the
formula
bar and enter the data, then do Ctrl+Enter rather than just enter.

--
regards,
Tom Ogilvy

"Harry" wrote in message
. ..
Helo

I think the Subject says it all. I'm putting values into a series
of
cells in a
column, about 10 different rows, and there are cells down below in
that
same
column of the same sheet that are USUALLY the same value. Rather
than
having to
re-type them again, I'd like to have them copied to the target cells
as
I
type
them in the first group of cells, but I still want to be able to
override
(over-write) the values that were copied to the target cells, as
they
are
not
ALWAYS the same.

Thanks

Harry Fine

Private Sub Worksheet_Change(ByVal Target As Range)
if target.columns.count 1 then exit sub
if target.column < 11 then exit sub
if Target(1).Row = 16 and Target(1).Row <= 22 then
application.EnableEvents = False
me.Range("K49:K54").Value = _
me.Range("K17:K22").Value
me.Range("K48").Value = me.Range("K16").Value _
& " " & me.Range("K17").Value
application.EnableEvents = True
End if
End Sub

If you want K49 empty then change

me.Range("K50:K54").Value = _
me.Range("K18:K22").Value

--
Regards,
Tom Ogilvy




"Harry" wrote in message
.. .
That was pretty cool. It works great. Tom, do you do this for a living,
and
what movitvates you to spend time writing stuff for new people like me.

May I impose on you one more time. Unfortunately, there is not a 1:1
relationship between the data in the k16-22 cells, and the data in the
k48-54
cells. There are two fields in the former, cells K16 & 17, Street # and
Street
Name, which are concatenated together below as Street Address in K48. Is
there
any way in the script you gave me to do something like:

me.Range("K48:K54").Value = _
me.Range("K16&17:K22").Value

Thanks

Harry


On Mon, 14 Feb 2005 13:55:52 -0500, "Tom Ogilvy" wrote:

That's correct Harry - but it works for the way I interpreted your
description. Have you ever heard the story of the 3 blind men trying to
describe an elephant.

so now, based on what you have told us so far

Private Sub Worksheet_Change(ByVal Target As Range)
if target.columns.count 1 then exit sub
if target.column < 11 then exit sub
if Target(1).Row = 16 and Target(1).Row <= 22 then
application.EnableEvents = False
me.Range("K48:K54").Value = _
me.Range("K16:K22").Value
application.EnableEvents = True
End if
End Sub

--
Regards,
Tom Ogilvy


"Harry" wrote in message
. ..
Sorry Tom, I missed that. But it can't possibly work. It will need to
know the
starting cell and range to grab it from (Column K, rows 16-22) and then
where to
start copying the data (Column K, rows 48 - 54)

Harry


On Mon, 14 Feb 2005 11:53:03 -0500, "Tom Ogilvy"
wrote:

As I said:

Right click on the sheet tab of this worksheet and select view code.
Paste
in code similar to the above.

See Chip Pearson's page on Events
http://www.cpearson.com/excel/events.htm

for tutorials/ getting started with macros:


David McRitchie's site


http://www.mvps.org/dmcritchie/excel....htm#tutorials


the vba tutorials are below the excel tutorials.


This has good info as well (for Excel)





http://msdn.microsoft.com/office/und...started/defaul
t
.aspx?pull=/library/en-us/odc_xl2003_ta/html/odc_super.asp

http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Regards,
Tom Ogilvy


"Harry" wrote in message
. ..
Tom, this is great, and I am greatful, but....where do I put this
routine?
Is
it a Visual Basic script, or a Macro. Where do I start.

Harry


On Mon, 14 Feb 2005 10:38:37 -0500, "Tom Ogilvy"
wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
if target.count 1 then exit sub
if target.column = 3 then
application.EnableEvents = False
target.Resize(10,1).Value = Target.Value
application.EnableEvents = True
End if
End Sub

Change the 10 to the number of row you want to fill. Change 3 to
reflect
the column where you want this behavior.

Right click on the sheet tab of this worksheet and select view
code.
Paste
in code similar to the above.

another approach is to select all the cells to be filled, go to the
formula
bar and enter the data, then do Ctrl+Enter rather than just enter.

--
regards,
Tom Ogilvy

"Harry" wrote in message
. ..
Helo

I think the Subject says it all. I'm putting values into a
series
of
cells in a
column, about 10 different rows, and there are cells down below
in
that
same
column of the same sheet that are USUALLY the same value. Rather
than
having to
re-type them again, I'd like to have them copied to the target
cells
as
I
type
them in the first group of cells, but I still want to be able to
override
(over-write) the values that were copied to the target cells, as
they
are
not
ALWAYS the same.

Thanks

Harry Fine

Thanks Tom, that was a great help.

Harry


On Mon, 14 Feb 2005 21:32:10 -0500, "Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
if target.columns.count 1 then exit sub
if target.column < 11 then exit sub
if Target(1).Row = 16 and Target(1).Row <= 22 then
application.EnableEvents = False
me.Range("K49:K54").Value = _
me.Range("K17:K22").Value
me.Range("K48").Value = me.Range("K16").Value _
& " " & me.Range("K17").Value
application.EnableEvents = True
End if
End Sub

If you want K49 empty then change

me.Range("K50:K54").Value = _
me.Range("K18:K22").Value

--
Regards,
Tom Ogilvy




"Harry" wrote in message
. ..
That was pretty cool. It works great. Tom, do you do this for a living,
and
what movitvates you to spend time writing stuff for new people like me.

May I impose on you one more time. Unfortunately, there is not a 1:1
relationship between the data in the k16-22 cells, and the data in the
k48-54
cells. There are two fields in the former, cells K16 & 17, Street # and
Street
Name, which are concatenated together below as Street Address in K48. Is
there
any way in the script you gave me to do something like:

me.Range("K48:K54").Value = _
me.Range("K16&17:K22").Value

Thanks

Harry


On Mon, 14 Feb 2005 13:55:52 -0500, "Tom Ogilvy" wrote:

That's correct Harry - but it works for the way I interpreted your
description. Have you ever heard the story of the 3 blind men trying to
describe an elephant.

so now, based on what you have told us so far

Private Sub Worksheet_Change(ByVal Target As Range)
if target.columns.count 1 then exit sub
if target.column < 11 then exit sub
if Target(1).Row = 16 and Target(1).Row <= 22 then
application.EnableEvents = False
me.Range("K48:K54").Value = _
me.Range("K16:K22").Value
application.EnableEvents = True
End if
End Sub

--
Regards,
Tom Ogilvy