Sorting Data

stirlingmw

Board Regular
Joined
Feb 18, 2013
Messages
75
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,063
Office Version
  1. 365
Platform
  1. Windows
Hiya
How about changing
Code:
Range("A3:C300").Select
to
Code:
Range("A3:[COLOR=#ff0000]AJ[/COLOR]300").Select
 

stirlingmw

Board Regular
Joined
Feb 18, 2013
Messages
75
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,063
Office Version
  1. 365
Platform
  1. Windows
Is this all the code or is there more?
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,063
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

stirlingmw

Board Regular
Joined
Feb 18, 2013
Messages
75
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,063
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Feb 18, 2013
Messages
75
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,063
Office Version
  1. 365
Platform
  1. Windows
Hello Steve
This will sort do the sort
Code:
    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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,063
Office Version
  1. 365
Platform
  1. Windows
Forget the above, instead try this in your command button
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top