Sorting data automatically

  • Thread starter Thread starter Graham Mason
  • Start date Start date
G

Graham Mason

I have a Workbook with a number of worksheets linked to a master sheet at
the front. If any data changes in any of the worksheets, I would like to
sort automatically on the master sheet, i.e. if a date changes I would like
to sort the dates in their new order. Can this be done?
Using Excel 2003
 
Record a macro to sort the data on the master sheet, and then add this event
code.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Call yourMacro
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

Just make sure that your macro activates the master sheet, then returns to
the activesheet. Something like

Dim returnPlace As Range

Set returnPlace = ActiveCell
Worksheets("Master").Activate
'do the sort
returnPlace.Parent.Activate
returnPlace.Activate


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
If you have the option to use a "helper" column to display your sorted
dates, this could be accomplished *automatically*.

For example. if A1 to A50 on your master sheet were linked to columns of
dates on the other sheets, you could enter this in B1:

=SMALL($A$1:$A$50,ROW())

And drag down to copy.
Make sure that Column B was formatted to display dates.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I have a Workbook with a number of worksheets linked to a master sheet at
the front. If any data changes in any of the worksheets, I would like to
sort automatically on the master sheet, i.e. if a date changes I would like
to sort the dates in their new order. Can this be done?
Using Excel 2003
 
Back
Top