|
link cells in multiple worksheets same workbook with excel vba
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 |
link cells in multiple worksheets same workbook with excel vba
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 |
link cells in multiple worksheets same workbook with excel vba
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 |
link cells in multiple worksheets same workbook with excel vba
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 |
link cells in multiple worksheets same workbook with excel vba
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 |
link cells in multiple worksheets same workbook with excel vba
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, |
link cells in multiple worksheets same workbook with excel vba
Hi Moon
Test this one Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook Dim lr As Long With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add On Error Resume Next Newsh.Name = "Summary-Sheet" If Err.Number 0 Then MsgBox "The Summary sheet already exist in this workbook." With Application .DisplayAlerts = False Newsh.Delete .DisplayAlerts = True .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With Exit Sub End If RwNum = 1 'The links to the first sheet will start in row 2 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then lr = Sh.Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row ColNum = 1 RwNum = RwNum + 1 Newsh.Cells(RwNum, 1).Value = Sh.Name 'Copy the sheet name in the A column For Each myCell In Sh.Range("A1,D5:E5,Z10") ' <----Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(False, False) Next myCell ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & Cells(lr, "D").Address(False, False) End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message oups.com... 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, |
link cells in multiple worksheets same workbook with excel vba
Hi Ron,
That works but when I update column C, it doesnt look at the last entry in it and gets the value of column D unless I rerun the macro. Have i asked too much? Moon |
link cells in multiple worksheets same workbook with excel vba
Have i asked too much?
Yes<g I will look at it tomorrow for you after work Bedtime for me -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message oups.com... Hi Ron, That works but when I update column C, it doesnt look at the last entry in it and gets the value of column D unless I rerun the macro. Have i asked too much? Moon |
link cells in multiple worksheets same workbook with excel vba
Hi Ron,
I figured it would be! I did learn a great deal from your code and it's really awesome to have gurus like you help newbies like me. Thanks! Moon |
link cells in multiple worksheets same workbook with excel vba
I will look at it now Moon
-- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message ups.com... Hi Ron, I figured it would be! I did learn a great deal from your code and it's really awesome to have gurus like you help newbies like me. Thanks! Moon |
link cells in multiple worksheets same workbook with excel vba
Do you have text or numbers in the C column Moon
-- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message ups.com... Hi Ron, I figured it would be! I did learn a great deal from your code and it's really awesome to have gurus like you help newbies like me. Thanks! Moon |
link cells in multiple worksheets same workbook with excel vba
Hi,
I have dates in that column. |
link cells in multiple worksheets same workbook with excel vba
You can try this
Add this formula in each sheet where you want (you can hide it also) =OFFSET(INDEX(C:C,MATCH(9.99999999999999E+307,C:C) ),1,1) Use this cell address in the origenal macro on my site http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message ups.com... Hi, I have dates in that column. |
link cells in multiple worksheets same workbook with excel vba
Hi,
I tried it but I'm probably doing something wrong. So do I place the formula anywhere in each sheet that I'd like to link the cell to? And you said to use this cell address; would that be the cell that I just placed the formula in. Thanks for clarification. Moon Ron de Bruin wrote: You can try this Add this formula in each sheet where you want (you can hide it also) =OFFSET(INDEX(C:C,MATCH(9.99999999999999E+307,C:C) ),1,1) Use this cell address in the origenal macro on my site http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message ups.com... Hi, I have dates in that column. |
link cells in multiple worksheets same workbook with excel vba
Hi Moon
If you copy the formula in Z1 for example it will display the value in the D column one cell below the last data in C Is that correct what you see ? Now you can use Z1 in the macro to build the links on the Summery sheet. It will always update if you add dates to the C column now -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message ups.com... Hi, I tried it but I'm probably doing something wrong. So do I place the formula anywhere in each sheet that I'd like to link the cell to? And you said to use this cell address; would that be the cell that I just placed the formula in. Thanks for clarification. Moon Ron de Bruin wrote: You can try this Add this formula in each sheet where you want (you can hide it also) =OFFSET(INDEX(C:C,MATCH(9.99999999999999E+307,C:C) ),1,1) Use this cell address in the origenal macro on my site http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message ups.com... Hi, I have dates in that column. |
link cells in multiple worksheets same workbook with excel vba
Hi Ron,
Thanks so much for all your help! That worked as you said. If its not too much, can you explain a bit about this part of the formula MATCH(9.99999999999999E+307 Thanks again! Moon Ron de Bruin wrote: Hi Moon If you copy the formula in Z1 for example it will display the value in the D column one cell below the last data in C Is that correct what you see ? Now you can use Z1 in the macro to build the links on the Summery sheet. It will always update if you add dates to the C column now -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message ups.com... Hi, I tried it but I'm probably doing something wrong. So do I place the formula anywhere in each sheet that I'd like to link the cell to? And you said to use this cell address; would that be the cell that I just placed the formula in. Thanks for clarification. Moon Ron de Bruin wrote: You can try this Add this formula in each sheet where you want (you can hide it also) =OFFSET(INDEX(C:C,MATCH(9.99999999999999E+307,C:C) ),1,1) Use this cell address in the origenal macro on my site http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message ups.com... Hi, I have dates in that column. |
link cells in multiple worksheets same workbook with excel vba
Read this page Moon
I think Bob and the late Frank Kabel did a great job http://www.xldynamic.com/source/xld.LastValue.html -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message oups.com... Hi Ron, Thanks so much for all your help! That worked as you said. If its not too much, can you explain a bit about this part of the formula MATCH(9.99999999999999E+307 Thanks again! Moon Ron de Bruin wrote: Hi Moon If you copy the formula in Z1 for example it will display the value in the D column one cell below the last data in C Is that correct what you see ? Now you can use Z1 in the macro to build the links on the Summery sheet. It will always update if you add dates to the C column now -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message ups.com... Hi, I tried it but I'm probably doing something wrong. So do I place the formula anywhere in each sheet that I'd like to link the cell to? And you said to use this cell address; would that be the cell that I just placed the formula in. Thanks for clarification. Moon Ron de Bruin wrote: You can try this Add this formula in each sheet where you want (you can hide it also) =OFFSET(INDEX(C:C,MATCH(9.99999999999999E+307,C:C) ),1,1) Use this cell address in the origenal macro on my site http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message ups.com... Hi, I have dates in that column. |
link cells in multiple worksheets same workbook with excel vba
Hi Ron,
Wow, thanks for everything--That was truly educational! Moon Ron de Bruin wrote: Read this page Moon I think Bob and the late Frank Kabel did a great job http://www.xldynamic.com/source/xld.LastValue.html -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message oups.com... Hi Ron, Thanks so much for all your help! That worked as you said. If its not too much, can you explain a bit about this part of the formula MATCH(9.99999999999999E+307 Thanks again! Moon Ron de Bruin wrote: Hi Moon If you copy the formula in Z1 for example it will display the value in the D column one cell below the last data in C Is that correct what you see ? Now you can use Z1 in the macro to build the links on the Summery sheet. It will always update if you add dates to the C column now -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message ups.com... Hi, I tried it but I'm probably doing something wrong. So do I place the formula anywhere in each sheet that I'd like to link the cell to? And you said to use this cell address; would that be the cell that I just placed the formula in. Thanks for clarification. Moon Ron de Bruin wrote: You can try this Add this formula in each sheet where you want (you can hide it also) =OFFSET(INDEX(C:C,MATCH(9.99999999999999E+307,C:C) ),1,1) Use this cell address in the origenal macro on my site http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message ups.com... Hi, I have dates in that column. |
link cells in multiple worksheets same workbook with excel vba
Wow, thanks for everything--That was truly educational!
You are welcome -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message oups.com... Hi Ron, Wow, thanks for everything--That was truly educational! Moon Ron de Bruin wrote: Read this page Moon I think Bob and the late Frank Kabel did a great job http://www.xldynamic.com/source/xld.LastValue.html -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message oups.com... Hi Ron, Thanks so much for all your help! That worked as you said. If its not too much, can you explain a bit about this part of the formula MATCH(9.99999999999999E+307 Thanks again! Moon Ron de Bruin wrote: Hi Moon If you copy the formula in Z1 for example it will display the value in the D column one cell below the last data in C Is that correct what you see ? Now you can use Z1 in the macro to build the links on the Summery sheet. It will always update if you add dates to the C column now -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message ups.com... Hi, I tried it but I'm probably doing something wrong. So do I place the formula anywhere in each sheet that I'd like to link the cell to? And you said to use this cell address; would that be the cell that I just placed the formula in. Thanks for clarification. Moon Ron de Bruin wrote: You can try this Add this formula in each sheet where you want (you can hide it also) =OFFSET(INDEX(C:C,MATCH(9.99999999999999E+307,C:C) ),1,1) Use this cell address in the origenal macro on my site http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Moon" wrote in message ups.com... Hi, I have dates in that column. |
| All times are GMT +1. The time now is 04:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com