Macro to replace a range of cell values

[SunshineStateBroker via OfficeKB.com]

Hello,
I have a bunch of excel files with several worksheets.

On the worksheet named TA in all files i have two cells R11 and R13, these
hold the row numbers of a begin and end point entered by the user
respectively.

I am having trouble writing a simple macro that will replace a range of
values in column K with the value of cell C2 after the user tabs out of
entering the value of R13.

C2 contains the formula =AVERAGE(B2:B150)

The range of K is determined by the values in R11 and R13.

Example:
C2 has the value .0013
R11 has the value 15
R13 has the value 25
After the user enters 25 in R13, cells K15 through K25 now have the value .
0013

Any assistance would be greatly approached. Thank you.

 

[Otto Moehrbach]

Not sure what you want to do with ALL the files, but the macro below will do
what you want in one file. This is an event macro and must be placed in the
sheet module of sheet TA. You can access that module by right-clicking on
the sheet tab and selecting View Code. "X" out of the module to return to
your sheet. Additional coding will be required if you want the code to loop
through all the files that you have. Note that I assumed that the values in
R11 & R13 are always integers. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TheRng As Range
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("R13")) Is Nothing Then
Set TheRng = Range(Cells(Range("R11").Value, 11),
Cells(Range("R13").Value, 11))
Range("C2").Copy
TheRng.PasteSpecial xlPasteValues
End If
End Sub

 

[SunshineStateBroker via OfficeKB.com]

The macro works great! Thank you very much.

When i was refering to several files, i didnt know if there was a way i could
run this specific macro in other similar files without having to paste the
code in each of the the Sheet TA's code areas when i am working on them.

Is there a way i can make this a global macro?

Not sure what you want to do with ALL the files, but the macro below will do
what you want in one file. This is an event macro and must be placed in the
sheet module of sheet TA. You can access that module by right-clicking on
the sheet tab and selecting View Code. "X" out of the module to return to
your sheet. Additional coding will be required if you want the code to loop
through all the files that you have. Note that I assumed that the values in
R11 & R13 are always integers. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TheRng As Range
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("R13")) Is Nothing Then
Set TheRng = Range(Cells(Range("R11").Value, 11),
Cells(Range("R13").Value, 11))
Range("C2").Copy
TheRng.PasteSpecial xlPasteValues
End If
End Sub

Hello,
I have a bunch of excel files with several worksheets.

[quoted text clipped - 20 lines]

Any assistance would be greatly approached. Thank you.

 

[SunshineStateBroker via OfficeKB.com]

Global as in:

If a workbook has a sheet named "TA" than this macro would run after a value
has been entered in R13.

(Sorry, i know this is a lot to ask)

The macro works great! Thank you very much.

When i was refering to several files, i didnt know if there was a way i could
run this specific macro in other similar files without having to paste the
code in each of the the Sheet TA's code areas when i am working on them.

Is there a way i can make this a global macro?

Not sure what you want to do with ALL the files, but the macro below will do
what you want in one file. This is an event macro and must be placed in the

[quoted text clipped - 19 lines]

 

[Otto Moehrbach]

No, I don't think that can be done. You can have a "global" macro in your
Personal.xls workbook that can be accessed by any other workbook, but not an
event macro like I gave you. You can put the meat of the macro I gave you
in the Personal.xls workbook, but you would still have to have an event
macro in each "TA" workbook to pick up on a change to R13 and call that
macro, so that wouldn't help you. Sorry. Otto

Global as in:

If a workbook has a sheet named "TA" than this macro would run after a
value
has been entered in R13.

(Sorry, i know this is a lot to ask)

The macro works great! Thank you very much.

When i was refering to several files, i didnt know if there was a way i
could
run this specific macro in other similar files without having to paste the
code in each of the the Sheet TA's code areas when i am working on them.

Is there a way i can make this a global macro?

Not sure what you want to do with ALL the files, but the macro below will
do
what you want in one file. This is an event macro and must be placed in
the

[quoted text clipped - 19 lines]

Any assistance would be greatly approached. Thank you.

 

[SunshineStateBroker via OfficeKB.com]

Gotcha.

Thank you again for everything.

No, I don't think that can be done. You can have a "global" macro in your
Personal.xls workbook that can be accessed by any other workbook, but not an
event macro like I gave you. You can put the meat of the macro I gave you
in the Personal.xls workbook, but you would still have to have an event
macro in each "TA" workbook to pick up on a change to R13 and call that
macro, so that wouldn't help you. Sorry. Otto

Global as in:

[quoted text clipped - 20 lines]