newbie ?: set variables, different shts, equal to each other

[terry b]

(This is a super-simple softball lofted to the group...)
I've got a (column) range in each of 2 sheets, call them TestSrc &
TestDest. I want to have the cells in TestDest dependent on the other
sht's range, so that whenever a value's entered in, for example,
TestSrc!B2, it instantly shows up in TestDest!C2. (I know I could
manually-select the destination-cell and enter "=TestSrc!B2", but
there's a couple-hundred 'source-cells' & I'd like to automate it.)
Also: the range of cells in TestSrc sheet is in one column but is not
contiguous. I feel comfortable w/ building the looping code for this,
but am stumbling over the very basic syntax of how to set one variable
on one sht equal to a var on another--and do it so that any changes on
source sht would instantly show up on the other w/out any copying,
pasting, etc.
What I've tried:
Sub LinkCellsDiffShts()
Dim SrcRge As Range
Dim DestRge As Range
Set SrcRge = Worksheets("TestSrc").Range("B2")
Set DestRge = Worksheets("TestDest").Range("C2")
Sheets("TestDest").Range(DestRge). _
Value = Sheets("TestSrc").Range(SrcRge).Value
End Sub

I'm getting error msges from this, & don't know how to fix it.
Any help would be greatly appreciated [)

terry b.

 

[Jim Cone]

terry b,

DestRge.Value = SrcRge.Value

Jim Cone
San Francisco, USA

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B:B")) Is Nothing Then
With Target
Worksheets("TestDest").Range(.Address).Offset(0, 1).Value =
..Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[terry b]

Hey, Jim,
Thanks for simplifying things! Your suggestion does what I was trying
to do in line 6, but evidently when I included the 'Sheets' & 'Range'
properties I was clouding things up for no good reason.... One other
question: When referring to a variable such as TestSrc, can you use
"Range(TestSrc)"? Whenever I try to do that, I get this: "Run-time
error '1004': Method 'Range' of object '_Global' failed"....Can't you
use a variable name as an argument in 'Range'?
Again, thanks much for the help.
terry b.

 

[terry b]

Hey, Bob [)
thanks for taking the time to reply! Way back when you were posting
(relatively-simple) answers to my questions about the Offset function
(exceltip.com, months ago), I found it pretty easy to follow what you
sd. But when you start talking about "worksheet event code" and
whatnot, I see that there's still a mountain of concepts for me to
grok, before I'm able to follow what you're trying to explain....
Have an excellent day!
terry b.

 

[Jim Cone]

terry b.

Yes you can use a variable when specifying a range.
Unfortunately you don't identify the data type of the variable
you are trying to use.
I try to use some sort of data type prefix on all variables, so that
when posting to a newsgroup or looking at the variable in the
787th line of the module, I or others will know what it is.

The are conventions for doing this, but something like these can help...
lng for Long
sng for Single
dbl for Double
rng for Range
shp for Shape
cmd for CommandBar
str for String
obj for Objects you don't have a prefix for

To answer your question...

If "TestSrc" is a range object established with code
something like...Set TestScr = Worksheets(1).Range("B5:B10")
then you use it by itself: TestScr.Interior.ColorIndex = 40

If "TestScr" is a String something like... TestScr = "Fudge" then it
cannot be used to identify a range.

However if "TestScr" is a Name that refers to a Range that was
established by using the Name box in the top left corner
of a worksheet OR
"TestSCr" is a name that refers to a Range that was
established by something like...
ActiveWorkbook.Names.Add Name:="TestScr", RefersTo:="=Sheet1!$A$1:$C$20"

Then use the name by enclosing it in quotation marks, your example
would become...Range("TestSrc")

I went on much too long, but hope it helps.

Regards,
Jim Cone
San Francisco, USA

 

[terry b]

Hello, Jim
I don't mind the detail; it all helps!
One last thing: What about the issue of the reference between the
source cell & the destination cell being a "live" one?
What I mean is, if there's value in sheet "TestSrc" cell B2, and I go
to sheet "TestDest" cell C2 and enter, "=TestSrc!B2", the destination
cell immediately takes on the value of the source cell, and 5 minutes
later if I alter the source cell, the dest. cell is
automatically-altered--without having to enter the function again. It's
a "live" connection....
BUT if I write a snippet of code like:
Sub LinkCellsDiffShts()
Dim rngSource As Range
Dim rngDestination As Range
Set rngSource = Worksheets("TestSrc").Range("B2")
Set rngDestination = Worksheets("TestDest").Range("C2")
rngDestination.Value = rngSource.Value
End Sub

yes, the destination cell takes on the source value, but if I
go back 5 minutes later and alter the source, the destination doesn't
change. Unless I run the code again. How does a person code a "live"
dependence between the cells?

terry b.

 

[Jim Cone]

terry b.

Question: "How does a person code a "live" dependence between the cells?"

You use code to add the formula to the destination cell.

rngDestination.Formula = "=TestSrc!B2"

Regards,
Jim Cone

 

[terry b]

Hello, Jim
Hard-coding the formula into the destination cell is understood. BUT
this brings me back to why I wanted to use variables to refer to the
source- and destination-cells. Because the 1st link is [shts change
every time] B2 > C2, next is B6 > C3, next is B10 > C4, next B14 > C5,
and so on for well over 50 entries. So I needed to use variables to
refer to source & destination, so that I could use For...Next or
Do...Loop, and step the respective variables the necessary increments.
Do you see what I'm saying? If I specifically-coded the first
destination-cell "=TestSrc!B2", then how would I increment the cell
upward every time w/out having to write the specific-code 50+ different
times?
terry b.

 

[Jim Cone]

terry b.

I think what we have here is "mission creep"? <g>
'--------------------------------------------------
Sub TestSomeMore()
Dim lngFirst As Long
Dim lngSecond As Long
Dim objSht1 As Excel.Worksheet
Dim objSht2 As Excel.Worksheet

Set objSht1 = Worksheets(1)
Set objSht2 = Worksheets(2)
lngSecond = 2

For lngFirst = 2 To 22 Step 4
objSht2.Cells(lngSecond, 2).Formula = "= " & objSht1.Name & "!" & _
objSht1.Cells(lngFirst, 2).Address
lngSecond = lngSecond + 1
Next 'lngFirst

End Sub
'-------------------------------------

Regards,
Jim Cone

 

[terry b]

Hey, Jim!
Thanks very, very much for putting up with this 'mission creep'
<pun>.
You've been beyond generous w/ your time, & have given me much to
think over....
Have an excellent Sunday night [)

terry b.

 

[Bob Phillips]

Terry,

You can, but isn't TestSrc and TestDest worksheet names in this case?

--

HTH

RP
(remove nothere from the email address if mailing direct)