pushing data from one worksheet to another

  • Thread starter Thread starter Jim A
  • Start date Start date
J

Jim A

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"...
 
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
 
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
 
The clarity provided by explicit assignment, IMO, far outweighs the extra
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
| 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 <[email protected]>,
|
| > FYI: you don't need the .Value on both the source and the target, only
on
| > the target.
 
What I meant in question 3 is if T2 i checked (or some value) then copy data
to one column, if U2 is checked then data is copied to a different column etc.
Thanks - Jim A
 
OK...works great! How can I push this data to other workbooks, preferably
not open workbooks?

This is very helpful - Jim A
 
Back
Top