Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to update information from one worksheet to another automatic
I have a workbook that contains 3 sheets: "Active Subcontractors", "In-Active
Subcontractors" & "Active Suncontractors-Sorted". This workbook is just to keep up with our subcontractors information, etc. When I update my main sheet, "Active Subcontractors", I would like for that information to automatically update in the "Active Subcontractors-Sorted" sheet. I would like the easiest way to make this happen. I really do not know anything about Macros & "linking" does not seem to be working, unless I am doing something wrong. I am working in Excel 2000. Thank you for any repsonses. :) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to update information from one worksheet to another automatic
Rather than having three worksheets why not have the data and then a pivot
table to show the data with an active inactive flag/column put the flag as a page type. then you only have to refresh the pivot table to get the data -- Hope this helps Martin Fishlock "Jenn" wrote: I have a workbook that contains 3 sheets: "Active Subcontractors", "In-Active Subcontractors" & "Active Suncontractors-Sorted". This workbook is just to keep up with our subcontractors information, etc. When I update my main sheet, "Active Subcontractors", I would like for that information to automatically update in the "Active Subcontractors-Sorted" sheet. I would like the easiest way to make this happen. I really do not know anything about Macros & "linking" does not seem to be working, unless I am doing something wrong. I am working in Excel 2000. Thank you for any repsonses. :) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to update information from one worksheet to another autom
Thanks Martin, I will give that a try.
"Martin Fishlock" wrote: Rather than having three worksheets why not have the data and then a pivot table to show the data with an active inactive flag/column put the flag as a page type. then you only have to refresh the pivot table to get the data -- Hope this helps Martin Fishlock "Jenn" wrote: I have a workbook that contains 3 sheets: "Active Subcontractors", "In-Active Subcontractors" & "Active Suncontractors-Sorted". This workbook is just to keep up with our subcontractors information, etc. When I update my main sheet, "Active Subcontractors", I would like for that information to automatically update in the "Active Subcontractors-Sorted" sheet. I would like the easiest way to make this happen. I really do not know anything about Macros & "linking" does not seem to be working, unless I am doing something wrong. I am working in Excel 2000. Thank you for any repsonses. :) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to update information from one worksheet to another autom
Martin,
That may be a bit more indepth than I'm going to need. I don't think I explained what I have very well. I have a workbook that contains our list of subcontractors & their info on one sheet labeled "Active Subs". I have a duplicate of that sheet labeled "Active Subs-Sorted", obviously this file will be an exact dup of the main "Active Subs" so I can sort the info as I chose. I would like to create some sort of link from "Active Subs" to "Active Subs-Sorted" so that everytime any change is made to the "Active Subs" file, in any cell, that change is made automatically to the "-Sorted" file. Thanks "Martin Fishlock" wrote: Rather than having three worksheets why not have the data and then a pivot table to show the data with an active inactive flag/column put the flag as a page type. then you only have to refresh the pivot table to get the data -- Hope this helps Martin Fishlock "Jenn" wrote: I have a workbook that contains 3 sheets: "Active Subcontractors", "In-Active Subcontractors" & "Active Suncontractors-Sorted". This workbook is just to keep up with our subcontractors information, etc. When I update my main sheet, "Active Subcontractors", I would like for that information to automatically update in the "Active Subcontractors-Sorted" sheet. I would like the easiest way to make this happen. I really do not know anything about Macros & "linking" does not seem to be working, unless I am doing something wrong. I am working in Excel 2000. Thank you for any repsonses. :) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to update information from one worksheet to another autom
Jenn:
Try this little macro. Place it in the code for the worksheet where the original data is (active subs), that is in the actual code for the sheet and not a serperate macro module: I have put most of the variables as constants so you just change these to meet your requirements. I have assumed that there is a header row and that the table starts at A1 in 'Active Subs - Sorted' '-------start------------ Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Const szSortedSheet As String = "Active Subs - Sorted" 'destination sheet Const szSortedTableStart As String = "A1" ' where it starts Const szSortColumnKey1 As String = "A" ' key column Dim wsSorted As Worksheet Set wsSorted = ThisWorkbook.Worksheets(szSortedSheet) Me.Cells.Copy wsSorted.Range(szSortedTableStart) wsSorted.Range(szSortedTableStart).CurrentRegion.S ort _ Key1:=wsSorted.Columns(szSortColumnKey1), _ Header:=xlYes set ws.Sorted = Nothing End Sub -- Hope this helps Martin Fishlock "Jenn" wrote: Martin, That may be a bit more indepth than I'm going to need. I don't think I explained what I have very well. I have a workbook that contains our list of subcontractors & their info on one sheet labeled "Active Subs". I have a duplicate of that sheet labeled "Active Subs-Sorted", obviously this file will be an exact dup of the main "Active Subs" so I can sort the info as I chose. I would like to create some sort of link from "Active Subs" to "Active Subs-Sorted" so that everytime any change is made to the "Active Subs" file, in any cell, that change is made automatically to the "-Sorted" file. Thanks "Martin Fishlock" wrote: Rather than having three worksheets why not have the data and then a pivot table to show the data with an active inactive flag/column put the flag as a page type. then you only have to refresh the pivot table to get the data -- Hope this helps Martin Fishlock "Jenn" wrote: I have a workbook that contains 3 sheets: "Active Subcontractors", "In-Active Subcontractors" & "Active Suncontractors-Sorted". This workbook is just to keep up with our subcontractors information, etc. When I update my main sheet, "Active Subcontractors", I would like for that information to automatically update in the "Active Subcontractors-Sorted" sheet. I would like the easiest way to make this happen. I really do not know anything about Macros & "linking" does not seem to be working, unless I am doing something wrong. I am working in Excel 2000. Thank you for any repsonses. :) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to update information from one worksheet to another autom
That's awesome. Thanks Martin
"Martin Fishlock" wrote: Jenn: Try this little macro. Place it in the code for the worksheet where the original data is (active subs), that is in the actual code for the sheet and not a serperate macro module: I have put most of the variables as constants so you just change these to meet your requirements. I have assumed that there is a header row and that the table starts at A1 in 'Active Subs - Sorted' '-------start------------ Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Const szSortedSheet As String = "Active Subs - Sorted" 'destination sheet Const szSortedTableStart As String = "A1" ' where it starts Const szSortColumnKey1 As String = "A" ' key column Dim wsSorted As Worksheet Set wsSorted = ThisWorkbook.Worksheets(szSortedSheet) Me.Cells.Copy wsSorted.Range(szSortedTableStart) wsSorted.Range(szSortedTableStart).CurrentRegion.S ort _ Key1:=wsSorted.Columns(szSortColumnKey1), _ Header:=xlYes set ws.Sorted = Nothing End Sub -- Hope this helps Martin Fishlock "Jenn" wrote: Martin, That may be a bit more indepth than I'm going to need. I don't think I explained what I have very well. I have a workbook that contains our list of subcontractors & their info on one sheet labeled "Active Subs". I have a duplicate of that sheet labeled "Active Subs-Sorted", obviously this file will be an exact dup of the main "Active Subs" so I can sort the info as I chose. I would like to create some sort of link from "Active Subs" to "Active Subs-Sorted" so that everytime any change is made to the "Active Subs" file, in any cell, that change is made automatically to the "-Sorted" file. Thanks "Martin Fishlock" wrote: Rather than having three worksheets why not have the data and then a pivot table to show the data with an active inactive flag/column put the flag as a page type. then you only have to refresh the pivot table to get the data -- Hope this helps Martin Fishlock "Jenn" wrote: I have a workbook that contains 3 sheets: "Active Subcontractors", "In-Active Subcontractors" & "Active Suncontractors-Sorted". This workbook is just to keep up with our subcontractors information, etc. When I update my main sheet, "Active Subcontractors", I would like for that information to automatically update in the "Active Subcontractors-Sorted" sheet. I would like the easiest way to make this happen. I really do not know anything about Macros & "linking" does not seem to be working, unless I am doing something wrong. I am working in Excel 2000. Thank you for any repsonses. :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update a column data with info from new worksheet | Excel Discussion (Misc queries) | |||
Automatic updating of a rollup worksheet when a new worksheet is a | Excel Worksheet Functions | |||
Opening Protected EXCEL worksheets to update linked data | Excel Discussion (Misc queries) | |||
Opening Protected EXCEL worksheets to update linked data | Setting up and Configuration of Excel | |||
How do I use excel information for a second excel worksheet | Excel Discussion (Misc queries) |