Automatically sorting by date over multiple sheets

Question

I am attempting to write/record my first macros and having a bit of trouble...

I am trying to sort a data range (B16:AK69 - date stored in Column B) by chronological date in each spread sheet (JUL, AUG, SEP...) **Data range same in each spreadsheet

When I use the record function - it ruins the formatting for all my surrounding analytics and the data table itself ie. the data from each row becomes muddled. 

I am a bit lost trying to debunk the code from the record function.

Would anyone be so kind to provide me with some basic code for this scenario?

I want to be able to use a key command to autosort the data range after input of new data. 

Many thanks,

Daria

Answer 1

I am attempting to write/record my first macros and having a bit of trouble...

Record a macro for that? Forget it, that's to difficult.

The first step is to make the macro work in one sheet, after that you can execute the same macro on (part of) all sheets.

Okay, the simplest basic sort is that, please read the help for Range.Sort:

Sub Test()

  Range("B16:AK69").Sort Range("B16")

End Sub

But that's static, you need a dynamic one:

Sub Test2()

  Dim R As Range

  'Find the last row in column B

  Set R = Range("B" & Rows.Count).End(xlUp)

  'Expand to the data rows

  Set R = Range("B16", R)

  'Expand to the columns

  Set R = Intersect(Range("B:AK"), R.EntireRow)

  'Sort

  R.Sort Range("B16")

End Sub

Please read the help on Range.End, Intersect, Range.EntireRow

Here's a link to a tutorial for beginners:

http://www.wiseowl.co.uk/blog/s161/online-excel-vba-training.htm

Play around with that code, if you have an idea how it works, come back and we can discuss further.

Andreas.