Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link Worksheets Into Master
Using Excel 2003 - We have worksheets with tabs called 'Sec 10', 'Sec 11',
etc. (up to Sec 61) that are updated by the individuals that work in those sections. We would like to link all of those worksheets into a master that will show all data in each section consolidated into one (with MANY rows!), and that will continually update the master as data is entered in each worksheet. All worksheets have the same column headers in Row 1 (NHBD, SEC, BLK, LOT, DATE, WHO, STATUS, NOTES). We would also like to keep the Master sorted by SEC then BLK, then LOT order. I don't know VBA or any advanced formulas, and have tried using consolidation, normal linking (which produces a number, not the individual rows of data). The people using this don't know how to use or have Access, so I need to figure out a simple way to do this in Excel. THANK YOU! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link Worksheets Into Master
See my site for a few examples
For example this one http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "PolQueen" wrote in message ... Using Excel 2003 - We have worksheets with tabs called 'Sec 10', 'Sec 11', etc. (up to Sec 61) that are updated by the individuals that work in those sections. We would like to link all of those worksheets into a master that will show all data in each section consolidated into one (with MANY rows!), and that will continually update the master as data is entered in each worksheet. All worksheets have the same column headers in Row 1 (NHBD, SEC, BLK, LOT, DATE, WHO, STATUS, NOTES). We would also like to keep the Master sorted by SEC then BLK, then LOT order. I don't know VBA or any advanced formulas, and have tried using consolidation, normal linking (which produces a number, not the individual rows of data). The people using this don't know how to use or have Access, so I need to figure out a simple way to do this in Excel. THANK YOU! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link Worksheets Into Master
I was able to use the ALT F11 and see where to put the code, so that was a
big help. I read through your examples, but since I don't know much about advanced commands in Macros, SQL or VBA, I have no idea what some of this does. It looks like I should use the part that says "Copy from Row 2 till the last row with data", since they will be continually filling in the individual worksheets. Where it says "ThisWorkbook.Worksheets ("MergeSheet")" should I be replacing that with names of all the tabs, or does the program just know because the code is in that workbook? Does the data in each tab need to have a range name first? Do I have to also include "Common Functions required for all routines" to make this work? Thank you for your help! "Ron de Bruin" wrote: See my site for a few examples For example this one http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "PolQueen" wrote in message ... Using Excel 2003 - We have worksheets with tabs called 'Sec 10', 'Sec 11', etc. (up to Sec 61) that are updated by the individuals that work in those sections. We would like to link all of those worksheets into a master that will show all data in each section consolidated into one (with MANY rows!), and that will continually update the master as data is entered in each worksheet. All worksheets have the same column headers in Row 1 (NHBD, SEC, BLK, LOT, DATE, WHO, STATUS, NOTES). We would also like to keep the Master sorted by SEC then BLK, then LOT order. I don't know VBA or any advanced formulas, and have tried using consolidation, normal linking (which produces a number, not the individual rows of data). The people using this don't know how to use or have Access, so I need to figure out a simple way to do this in Excel. THANK YOU! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link Worksheets Into Master
Hi
Copy this in a normal module of your workbook Alt F11 Insert module Pste the code Alt q to go back to excel When you use Alt F8 in Excel run the macro You not have to chnage things in the code Sub Test2() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim shLast As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "MergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("MergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "MergeSheet" Set DestSh = ThisWorkbook.Worksheets.Add DestSh.Name = "MergeSheet" 'loop through all worksheets and copy the data to the DestSh For Each sh In ThisWorkbook.Worksheets If sh.Name < DestSh.Name Then Last = LastRow(DestSh) shLast = LastRow(sh) 'This example copies everything, if you only want to copy 'values/formats look at the example below the first example sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy DestSh.Cells(Last + 1, "A") End If Next Application.Goto DestSh.Cells(1) With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "PolQueen" wrote in message ... I was able to use the ALT F11 and see where to put the code, so that was a big help. I read through your examples, but since I don't know much about advanced commands in Macros, SQL or VBA, I have no idea what some of this does. It looks like I should use the part that says "Copy from Row 2 till the last row with data", since they will be continually filling in the individual worksheets. Where it says "ThisWorkbook.Worksheets ("MergeSheet")" should I be replacing that with names of all the tabs, or does the program just know because the code is in that workbook? Does the data in each tab need to have a range name first? Do I have to also include "Common Functions required for all routines" to make this work? Thank you for your help! "Ron de Bruin" wrote: See my site for a few examples For example this one http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "PolQueen" wrote in message ... Using Excel 2003 - We have worksheets with tabs called 'Sec 10', 'Sec 11', etc. (up to Sec 61) that are updated by the individuals that work in those sections. We would like to link all of those worksheets into a master that will show all data in each section consolidated into one (with MANY rows!), and that will continually update the master as data is entered in each worksheet. All worksheets have the same column headers in Row 1 (NHBD, SEC, BLK, LOT, DATE, WHO, STATUS, NOTES). We would also like to keep the Master sorted by SEC then BLK, then LOT order. I don't know VBA or any advanced formulas, and have tried using consolidation, normal linking (which produces a number, not the individual rows of data). The people using this don't know how to use or have Access, so I need to figure out a simple way to do this in Excel. THANK YOU! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link Worksheets Into Master
Hi Ron!
Thank you so much for your help! That code worked perfectly, and we have a bunch of "happy campers" here. I have got to learn how to do that myself someday. Take care, PolQueen "Ron de Bruin" wrote: Hi Copy this in a normal module of your workbook Alt F11 Insert module Pste the code Alt q to go back to excel When you use Alt F8 in Excel run the macro You not have to chnage things in the code Sub Test2() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim shLast As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "MergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("MergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "MergeSheet" Set DestSh = ThisWorkbook.Worksheets.Add DestSh.Name = "MergeSheet" 'loop through all worksheets and copy the data to the DestSh For Each sh In ThisWorkbook.Worksheets If sh.Name < DestSh.Name Then Last = LastRow(DestSh) shLast = LastRow(sh) 'This example copies everything, if you only want to copy 'values/formats look at the example below the first example sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy DestSh.Cells(Last + 1, "A") End If Next Application.Goto DestSh.Cells(1) With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "PolQueen" wrote in message ... I was able to use the ALT F11 and see where to put the code, so that was a big help. I read through your examples, but since I don't know much about advanced commands in Macros, SQL or VBA, I have no idea what some of this does. It looks like I should use the part that says "Copy from Row 2 till the last row with data", since they will be continually filling in the individual worksheets. Where it says "ThisWorkbook.Worksheets ("MergeSheet")" should I be replacing that with names of all the tabs, or does the program just know because the code is in that workbook? Does the data in each tab need to have a range name first? Do I have to also include "Common Functions required for all routines" to make this work? Thank you for your help! "Ron de Bruin" wrote: See my site for a few examples For example this one http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "PolQueen" wrote in message ... Using Excel 2003 - We have worksheets with tabs called 'Sec 10', 'Sec 11', etc. (up to Sec 61) that are updated by the individuals that work in those sections. We would like to link all of those worksheets into a master that will show all data in each section consolidated into one (with MANY rows!), and that will continually update the master as data is entered in each worksheet. All worksheets have the same column headers in Row 1 (NHBD, SEC, BLK, LOT, DATE, WHO, STATUS, NOTES). We would also like to keep the Master sorted by SEC then BLK, then LOT order. I don't know VBA or any advanced formulas, and have tried using consolidation, normal linking (which produces a number, not the individual rows of data). The people using this don't know how to use or have Access, so I need to figure out a simple way to do this in Excel. THANK YOU! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link Worksheets Into Master
Hi PolQueen
Thanks for the feedback -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "PolQueen" wrote in message ... Hi Ron! Thank you so much for your help! That code worked perfectly, and we have a bunch of "happy campers" here. I have got to learn how to do that myself someday. Take care, PolQueen "Ron de Bruin" wrote: Hi Copy this in a normal module of your workbook Alt F11 Insert module Pste the code Alt q to go back to excel When you use Alt F8 in Excel run the macro You not have to chnage things in the code Sub Test2() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim shLast As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "MergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("MergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "MergeSheet" Set DestSh = ThisWorkbook.Worksheets.Add DestSh.Name = "MergeSheet" 'loop through all worksheets and copy the data to the DestSh For Each sh In ThisWorkbook.Worksheets If sh.Name < DestSh.Name Then Last = LastRow(DestSh) shLast = LastRow(sh) 'This example copies everything, if you only want to copy 'values/formats look at the example below the first example sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy DestSh.Cells(Last + 1, "A") End If Next Application.Goto DestSh.Cells(1) With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "PolQueen" wrote in message ... I was able to use the ALT F11 and see where to put the code, so that was a big help. I read through your examples, but since I don't know much about advanced commands in Macros, SQL or VBA, I have no idea what some of this does. It looks like I should use the part that says "Copy from Row 2 till the last row with data", since they will be continually filling in the individual worksheets. Where it says "ThisWorkbook.Worksheets ("MergeSheet")" should I be replacing that with names of all the tabs, or does the program just know because the code is in that workbook? Does the data in each tab need to have a range name first? Do I have to also include "Common Functions required for all routines" to make this work? Thank you for your help! "Ron de Bruin" wrote: See my site for a few examples For example this one http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "PolQueen" wrote in message ... Using Excel 2003 - We have worksheets with tabs called 'Sec 10', 'Sec 11', etc. (up to Sec 61) that are updated by the individuals that work in those sections. We would like to link all of those worksheets into a master that will show all data in each section consolidated into one (with MANY rows!), and that will continually update the master as data is entered in each worksheet. All worksheets have the same column headers in Row 1 (NHBD, SEC, BLK, LOT, DATE, WHO, STATUS, NOTES). We would also like to keep the Master sorted by SEC then BLK, then LOT order. I don't know VBA or any advanced formulas, and have tried using consolidation, normal linking (which produces a number, not the individual rows of data). The people using this don't know how to use or have Access, so I need to figure out a simple way to do this in Excel. THANK YOU! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create a list and link it to the master excel file | Excel Worksheet Functions | |||
Link different worksheets in different workbooks to 1 master workb | Excel Discussion (Misc queries) | |||
How do I link password protected files to a master spreadsheet? | Excel Worksheet Functions | |||
Link Worksheets to a Master Sheet | Excel Worksheet Functions | |||
how do i link a number of worksheets to one master worksheet? | Excel Worksheet Functions |