Hi all,
I'm trying to link some cells that are in different worksheets (i have
alot of sheets) in the same workbook to a Master worksheet in the same
workbook. I'd like to use vba to do this. The cells to link to are the
same in all worksheets. So for example, in Master worksheet, I need C2
to link to sheet1.C3 and C3 to link to sheet2.C3
Can anyone point me in the right direction on how to do this?
Thanks!
Moon

Hi Moon

Try this
http://www.rondebruin.nl/summary.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Moon" wrote in message oups.com...
Hi all,
I'm trying to link some cells that are in different worksheets (i have
alot of sheets) in the same workbook to a Master worksheet in the same
workbook. I'd like to use vba to do this. The cells to link to are the
same in all worksheets. So for example, in Master worksheet, I need C2
to link to sheet1.C3 and C3 to link to sheet2.C3
Can anyone point me in the right direction on how to do this?
Thanks!
Moon

Hi Ron,
Thanks so much for your help! That works so perfectly. I do have
another question though that I dont have the expertise to figure out. I
have a cell that I want to link to a cell in another worksheet that is
one cell below the adjacent cell of the last entry in a column. For
example, if my last entry in column C is C5, I want to link to cell D6
This sounds pretty complicated but any help would be greatly
appreciated.
Thanks again,
Moon

Hi Moon

Try this

I use The C column in Sheet2 and in the example I add the formula in the activecell

Sub test()
Dim lr As Long
lr = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row
ActiveCell.Formula = _
"='Sheet2'!" & Cells(lr, "C").Address(False, False)
End Sub




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Moon" wrote in message oups.com...
Hi Ron,
Thanks so much for your help! That works so perfectly. I do have
another question though that I dont have the expertise to figure out. I
have a cell that I want to link to a cell in another worksheet that is
one cell below the adjacent cell of the last entry in a column. For
example, if my last entry in column C is C5, I want to link to cell D6
This sounds pretty complicated but any help would be greatly
appreciated.
Thanks again,
Moon

Oops, you want it in D

Sub test()
Dim lr As Long
lr = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row
ActiveCell.Formula = _
"='Sheet2'!" & Cells(lr, "D").Address(False, False)
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
Hi Moon

Try this

I use The C column in Sheet2 and in the example I add the formula in the activecell

Sub test()
Dim lr As Long
lr = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row
ActiveCell.Formula = _
"='Sheet2'!" & Cells(lr, "C").Address(False, False)
End Sub




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Moon" wrote in message oups.com...
Hi Ron,
Thanks so much for your help! That works so perfectly. I do have
another question though that I dont have the expertise to figure out. I
have a cell that I want to link to a cell in another worksheet that is
one cell below the adjacent cell of the last entry in a column. For
example, if my last entry in column C is C5, I want to link to cell D6
This sounds pretty complicated but any help would be greatly
appreciated.
Thanks again,
Moon

Hi Ron,
Thanks for the help. So I tried to link it to column D from all
worksheets into Summary-Sheet like this:

For Each myoffCell In Sh.Range("C" & Rows.Count).End(xlUp).Offset(1,
0).Row
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & Cells(lr, "D").Address(False,
False)
Next myoffCell

But For statement only works for collection or an array. I'm not sure
how to go ahead.
Thanks again,