Using Macro to put rows in multiple sheets in ascending order

[harjas]

hello,

I am looking to create a sheet that automatically updates itself in alphabetical order, when we enter any data.

here is the case:
- I've 30 secondary sheets and 1 master sheet
-----------------------------------------------------sheets detail-----------------------------
Master Sheet detail:

• master sheet's column A contains name from A3 to A100 (user can edit this column)
• Column B to to AM contains data corresponding to each name in column A

Secondary sheets detail:

• column A contains name from A3 to A100 which are linked to master's column A (means any change in Master sheets' column A will change this column).................. (user cannot edit this column)
• column B to I contains corresponding data and summary of these columns are reflected in master sheet's column B to AE (Rest of the columns of master sheet i.e from AF to AM is used for further calculations )

----------------------------------------------------------------------------------------------

I want that when user enters a new Name in master sheet and presses "Enter" then:-
Column A of master sheet should automatically arrange itself in ascending order, along with all the corresponding data in column B to AM
since secondary sheet's column A is also linked with master sheet, so they should also be updated along with their data in corresponding columns i.e B to I

thanks in advance

[tommyt61]

I will assume you are doing a formula in your secondary sheets that picks up the values in the master sheet .
In a seconday sheet you have a formula like this in A1 =MasterSheet1!A1 and in B1 =MasterSheet1!B1 etc...

if this is the case all you have to do is use this code to Sort your master sheet range A!:AM100 in Ascending order.


Note: this does not work on them just hitting the enter key . I think you would cause a bunch of unexpected troubles if you go that route. make you a shortcut key combination to run the code or put a button control on your master sheet and assign the macro to it.


You will also need to change Sheet1 in the code to match the name of your Master Sheet name.

Sub Sort_Asc()
'
' Sort_Asc Macro
'
'
    Columns("A:AM").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:AM100")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
    
    
End Sub

[harjas]

The CODE is running fine. This is the result:
Master Sheet:

• Columns A to AE are correctly sorted
• Column AF to AM shows wrong data, since these columns fetch data from secondary sheet's column B:I

Secondary Sheets:

• Column A is sorted correctly (since it is linked to Master sheet's column A)
• Columns B to I are not sorted (obviously coz no code is executed for secondary sheet's assortment)

If I use the code for sorting secondary sheets then, all the non empty cells of column A are arranged at top. 55 out of 100 are filled, rest are blank and since all 100 cells contains formula to be linked with master sheet, so excel is considering them to be "Used" cell.

[tommyt61]

upload a file with some data in it so i or someone else can maybe make sense of how it is all laid out and supposed to function.

[harjas]

I was making some change in the worksheet.

• Now only sheet 1's column A is providing data to all the sheets
• sorted all content in descending order (in order to escape empty cells)

here's the file:
Excel Program.zip

[nevadaDave]

So here is my solution.
But like tommy161, I would suggest using a button control to run the macro.
You didn't specify whether the user was modifying existing data or adding new data.





Put this code in the main worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)


Dim lastRow As Integer
Dim newRow As Integer

Sheets("main").Select 'Inusre the main sheet is selcted
lastRow = ActiveCell.SpecialCells(xlLastCell).Row 'find the last row

'Create link to new row on secondary sheets

'Did user use the "enter" key or the "tab" key
If ActiveCell.Column = 1 Then 'if
newRow = ActiveCell.Offset(-1, 0).Row 'if "Enter" key
Else
newRow = ActiveCell.Row 'if "tab" key
End If

Range("A" & newRow & ":I" & newRow).Copy 'copy the new data fields
Sheets("secondary1").Select 'select the appropriate worksheet
'Find the next row at the end of the data
ActiveSheet.Range("A" & ActiveCell.SpecialCells(xlLastCell).Row).Select
If Not (IsEmpty(ActiveCell)) Then ActiveCell.Offset(1, 0).Select

ActiveSheet.Paste Link:=True 'Create the links to the master sheet
'Select the master sheet and perform the sort on the data rows only.
Sheets("main").Select
Range("A3:AM" & lastRow).Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

[tommyt61]

I'm still not sure what all you are wanting to be able to sort. If NevadaDaves solution is not what you were trying to do the only thing i could suggest would be to use sheet named 1 as your master list for names. then add this to your Master sheet. in cell A3 enter =IF('1'!A3="","",'1'!A3) and copy down . In this scenario the only sheet you can sort is the Master Sheet and keep all the data correct. If your trying to be able sort any of the sheets 1 - 31 and keep all the data across all sheets correct .... well i'll leave that to someone else.

[harjas]

thanks for your help NevadaDaves and tommy61, i guess i got sufficient hint and i can now use these codes to get on with my program.