Sorting Data

[stirlingmw]

I have a workbook with the first spreadsheet being a "Nominal" of all my employees. This contains, Name, Initial and Age (Columns A to C)

my next 12 sheets equate to a month of the year Jan to Dec. The first 3 Columns in these sheets refer to the first 3 Columns in the "Nominal" sheet, returning the employees info.

The rest of the Columns in the month sheets (F to AJ) contains other info about the employee.

When I add a new employee to the bottom of the list I would then like the list to be sorted Ascending Alphabetically. This I have done with a CommandButton and macro

Sub SortName()
'
' SortName Macro
'


'
Range("A3:C300").Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Key2:=Range("A3") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("A3").Select
End Sub

This sorts the "Nominal" and also the other 12 sheets alphabetically, however in these other 12 sheets which contains more data about the employee for that month this Data remains in its original row and does not change with the employee, so now I have the correct Name, Initial and age of the employee, but the data in the data in Columns F to AJ could belong to someone else.

Please help, this is driving me mad.

Stirling

 

[Fluff]

Hiya
How about changing

Range("A3:C300").Select

to

Range("A3:[COLOR=#ff0000]AJ[/COLOR]300").Select

 

[stirlingmw]

I tried it earlier, but it doesnt work. Columns C to AJ on the nominal sheet are not on the other 12 month sheets.

For instance Name "Cass" Initial "AJ Age "16" is in Cells A22, B22 and C22 and Name "Conner" Initial "T" and Age "25" is in Cells A23, B23 and C23 on the nominal list. These two employees data appears on all the other 12 spreadsheets ("Jan, "Feb", "Mar" etc) in Columns A, B and C Rows 22 and 23.

However when I add Name "Corden" Initial "P" Age "24" to the "Nominal" spreadsheet and sort by Name ascending order Conner and his 3 columns of data from "Nominal" is now moved down a Row to Row 24 as Corden now occupies Row 23. Any data that I have added in Row 23 of say "Jan" Spreadsheet pertaining to Conner before I added Corden remains in Row 23 and appears now to belong to Corden.

I need to Sort all Columns F to AJ of Nominal and have Columns F to AJ of the other 12 months alter too. At present there is no reference to Columns F to AJ in any of the 12 months Spreadsheets.

 

[Fluff]

Is this all the code or is there more?

Sub SortName()
'
' SortName Macro
'


'
[COLOR=#0000ff]Range("A3:C300").Select[/COLOR]
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, [COLOR=#ff0000]Key2:=Range("A3") _
, Order2:=xlAscending,[/COLOR] Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal[COLOR=#ff0000], [/COLOR][COLOR=#ff0000]DataOption2 _
:=xlSortNormal[/COLOR]
Range("A3").Select
End Sub

Because changing the line in blue to read AJ300 rather than C300 & then running it on one of the month sheets should do what you want.
You are also sorting on the same value twice so the code in red can be removed

 

[Fluff]

Had another thought.
On the monthly sheets are the 1st three columns formulas or hard values?

 

[stirlingmw]

1st 3 are formulas
=IF(ISBLANK(Nominal!$A1),"",Nominal!$A1)
similar for columns B and C. I have done it like this, so I only add the data once on a Nominal page and not have to do the same to all 12 pages. The person who will be adding this data later in the year is not as computer literate and therefore I am trying to automate it for her best I can.

Answer to your previous, this is the only code.

 

[Fluff]

1st 3 are formulas

That's the reason why only those columns are being sorted.
I don't know much about formulas, so not sure if you can achieve what you want that way.
However it can be done with VBA, so on Monday I'll have a look at that, unless anyone posts a formula version before then.

 

[stirlingmw]

Fluff

thanks for the reply, I believe I have solved the problem I have been having.

Instead of using a formula to reference a cell from the Nominal spreadsheet I am using a Userform to add the relevant data to ALL of the spreadsheets at once.

Private Sub CommandButton1_Click()


Dim LRJan As Long, lrFeb As Long, lrMar As Long, lrApr As Long, lrMay As Long, lrJun As Long, lrJul As Long, lrAug As Long, lrSep As Long, lrOct As Long, lrNov As Long, lrDec As Long, lrNominal As Long




LRJan = Sheets("Jan").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Jan").Cells(LRJan + 1, "A").Value = TextBox1.Text
Sheets("Jan").Cells(LRJan + 1, "B").Value = TextBox2.Text
Sheets("Jan").Cells(LRJan + 1, "C").Value = TextBox3.Text


lrFeb = Sheets("Feb").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Feb").Cells(lrFeb + 1, "A").Value = TextBox1.Text
Sheets("Feb").Cells(lrFeb + 1, "B").Value = TextBox2.Text
Sheets("Feb").Cells(lrFeb + 1, "C").Value = TextBox3.Text

etc.

All I need to work out now is what code I add to this to sort ALL the sheets (Jan, Feb, Mar etc and also Nominal) by Name in ascending order Columns A to AJ. There is data in the first 3 Rows which does not need to be sorted.

Thanks

Steve

 

[Fluff]

Hello Steve
This will sort do the sort

    Dim Ws As Variant

    For Each Ws In Sheets
        Ws.Activate
        Range("A3:AJ300").Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Next Ws

With regard to your code for the userform, will all the sheets have the same number of rows?

 

[Fluff]

Forget the above, instead try this in your command button

Private Sub CommandButton1_Click()

    Dim Ws As Variant
    Dim LstRw As Long

    For Each Ws In Sheets
        Ws.Activate
        LstRw = Range("A" & Rows.Count).End(xlUp).Row
        Range("A" & LstRw + 1).Value = Textbox1.Text
        Range("B" & LstRw + 1).Value = Textbox2.Text
        Range("C" & LstRw + 1).Value = Textbox3.Text

        Range("A3:AJ" & LstRw + 1).Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Next Ws

End Sub