|
|
Hi,
I am new to VBA. I was given this to try(probably from this forum):
Private Sub Worksheet_Change(ByVal Target As Range)
Set r1 = Range("T2")
Set r2 = Range("AR5:AR47")
If Intersect(Target, r1) Is Nothing Then Exit Sub
dsheet = "sheet2"
Application.EnableEvents = False
r2.Copy Sheets(dsheet).Range("R7:R49")
Application.EnableEvents = True
End Sub
QUESTIONS-
1. This copies the formulas of AR5:AR47 How do I get it to copy the values
and NOT the formulas?
2. Why do I need "dsheet"?
3. Is there an easy way to copy AR5:AR47 to different colums on the same
sheet if T2, U2, V2, or W2 has a value in them one at a time?
Thanks for any help - Jim A
|
|
One way:
1)
Private Sub Worksheet_Change(ByVal Target as Range)
If Not Intersect(Target, Range("T2")) Is Nothing Then
Sheets("Sheet2").Range("R7:R49").Value = _
Me.Range("AR5:AR47").Value
End If
End Sub
2) You don't - it doesn't really add anything as it's used.
3) Not sure what you mean by "has a value in them one at a time"...
In article <19A7068B-782E-4408-A340-1A50FCEF38AD[ at ]microsoft.com>,
Jim A <Jim A[ at ]discussions.microsoft.com> wrote:
[Quoted Text] > Hi,
> I am new to VBA. I was given this to try(probably from this forum):
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Set r1 = Range("T2")
> Set r2 = Range("AR5:AR47")
> If Intersect(Target, r1) Is Nothing Then Exit Sub
> dsheet = "sheet2"
> Application.EnableEvents = False
> r2.Copy Sheets(dsheet).Range("R7:R49")
> Application.EnableEvents = True
> End Sub
>
> QUESTIONS-
> 1. This copies the formulas of AR5:AR47 How do I get it to copy the values
> and NOT the formulas?
> 2. Why do I need "dsheet"?
> 3. Is there an easy way to copy AR5:AR47 to different colums on the same
> sheet if T2, U2, V2, or W2 has a value in them one at a time?
>
> Thanks for any help - Jim A
|
|
Hi,
FYI: you don't need the .Value on both the source and the target, only on
the target.
Regarding you last question - the basic idea would be an If statement, but
it is not clear what you mean, so something like this:
If [T2]<>"" then
'Copy to another location
Else
'Your original copy command
End If
"JE McGimpsey" <jemcgimpsey[ at ]mvps.org> wrote in message
news:jemcgimpsey-CC6CD3.22424830122008[ at ]news.microsoft.com...
[Quoted Text] > One way:
>
> 1)
>
> Private Sub Worksheet_Change(ByVal Target as Range)
> If Not Intersect(Target, Range("T2")) Is Nothing Then
> Sheets("Sheet2").Range("R7:R49").Value = _
> Me.Range("AR5:AR47").Value
> End If
> End Sub
>
> 2) You don't - it doesn't really add anything as it's used.
>
> 3) Not sure what you mean by "has a value in them one at a time"...
>
>
>
> In article <19A7068B-782E-4408-A340-1A50FCEF38AD[ at ]microsoft.com>,
> Jim A <Jim A[ at ]discussions.microsoft.com> wrote:
>
>> Hi,
>> I am new to VBA. I was given this to try(probably from this forum):
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Set r1 = Range("T2")
>> Set r2 = Range("AR5:AR47")
>> If Intersect(Target, r1) Is Nothing Then Exit Sub
>> dsheet = "sheet2"
>> Application.EnableEvents = False
>> r2.Copy Sheets(dsheet).Range("R7:R49")
>> Application.EnableEvents = True
>> End Sub
>>
>> QUESTIONS-
>> 1. This copies the formulas of AR5:AR47 How do I get it to copy the
>> values
>> and NOT the formulas?
>> 2. Why do I need "dsheet"?
>> 3. Is there an easy way to copy AR5:AR47 to different colums on the same
>> sheet if T2, U2, V2, or W2 has a value in them one at a time?
>>
>> Thanks for any help - Jim A
|
|
Huh?
You don't "need" .Value on *either* source or target.
Since .Value is the default property for the range object, the target
range object's .Value property will be implied, too.
If anything, it's more important to specify the desired property for the
*source* object. For instance, if the source range contains a formula:
Range("target") = Range("source").Value
Range("target") = Range("source").Formula
Range("target") = Range("source").Text
can very easily produce three distinctly different results, while
Range("target").Value = Range("source")
Range("target").Formula = Range("source")
won't make a bit of difference.
Explicit specification of properties, is generally considered better
practice than using defaults, at least among the other professional
coders I know, and is required by the standards of most of my clients
(rather, most of the ones that *have* standards - too many don't).
The clarity provided by explicit assignment, IMO, far outweighs the
extra typing.
Just my US$0.02. YMMV
In article <ur36I2xaJHA.2124[ at ]TK2MSFTNGP04.phx.gbl>,
"Shane Devenshire" <shanedevenshire[ at ]sbcglobal.net> wrote:
[Quoted Text] > FYI: you don't need the .Value on both the source and the target, only on
> the target.
|
|
|
[Quoted Text] >>The clarity provided by explicit assignment, IMO, far outweighs the extra
>>typing.
Amen. I think many people who use the shortcut don't understand that is
what they're doing. I can't believe an MVP would advocate doing so.
And here's a case where it causes a problem:
Sub Works()
Sheets("Sheet1").Range("B1:B2").Clear
Sheets("Sheet1").Range("B1:B2").Value = _
Sheets("Sheet2").Range("A1:A2").Value
End Sub
Sub NoWork()
Sheets("Sheet1").Range("B1:B2").Clear
Sheets("Sheet1").Range("B1:B2") = _
Sheets("Sheet2").Range("A1:A2")
End Sub
--
Jim
"JE McGimpsey" <jemcgimpsey[ at ]mvps.org> wrote in message
news:jemcgimpsey-88A0AE.08243231122008[ at ]news.microsoft.com...
| Huh?
|
| You don't "need" .Value on *either* source or target.
|
| Since .Value is the default property for the range object, the target
| range object's .Value property will be implied, too.
|
| If anything, it's more important to specify the desired property for the
| *source* object. For instance, if the source range contains a formula:
|
| Range("target") = Range("source").Value
| Range("target") = Range("source").Formula
| Range("target") = Range("source").Text
|
| can very easily produce three distinctly different results, while
|
| Range("target").Value = Range("source")
| Range("target").Formula = Range("source")
|
| won't make a bit of difference.
|
| Explicit specification of properties, is generally considered better
| practice than using defaults, at least among the other professional
| coders I know, and is required by the standards of most of my clients
| (rather, most of the ones that *have* standards - too many don't).
|
| The clarity provided by explicit assignment, IMO, far outweighs the
| extra typing.
|
| Just my US$0.02. YMMV
|
| In article <ur36I2xaJHA.2124[ at ]TK2MSFTNGP04.phx.gbl>,
| "Shane Devenshire" <shanedevenshire[ at ]sbcglobal.net> wrote:
|
| > FYI: you don't need the .Value on both the source and the target, only
on
| > the target.
|
|
|