Copy 'value' of cell to other cell, same sheet after entering value

[Harry]

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 Ogilvy]

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.

 

[Harry]

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

 

[Tom Ogilvy]

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/excel.htm#tutorials


the vba tutorials are below the excel tutorials.


This has good info as well (for Excel)



http://msdn.microsoft.com/office/un...ibrary/en-us/odc_xl2003_ta/html/odc_super.asp

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

 

[Harry]

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

 

[Tom Ogilvy]

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

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

As I said:


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/excel.htm#tutorials


the vba tutorials are below the excel tutorials.


This has good info as well (for Excel)



http://msdn.microsoft.com/office/understanding/excel/gettingstarted/default

..aspx?pull=/library/en-us/odc_xl2003_ta/html/odc_super.asp

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

--
Regards,
Tom Ogilvy

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

 

[Harry]

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

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

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

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/excel.htm#tutorials


the vba tutorials are below the excel tutorials.


This has good info as well (for Excel)



http://msdn.microsoft.com/office/understanding/excel/gettingstarted/default

.aspx?pull=/library/en-us/odc_xl2003_ta/html/odc_super.asp

 

[Tom Ogilvy]

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

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

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

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

 

[Harry]

Thanks Tom, that was a great help.

Harry

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