Need to update information from one worksheet to another automatic

J

Jenn

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. :)
 
M

Martin Fishlock

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
 
J

Jenn

Thanks Martin, I will give that a try.

Martin Fishlock said:
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
Click to expand...
 
J

Jenn

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
 
M

Martin Fishlock

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.Sort _
Key1:=wsSorted.Columns(szSortColumnKey1), _
Header:=xlYes

set ws.Sorted = Nothing
End Sub
 
Top