Help re Date Sort with New Entry AND Date carried forward from previoius sheet, ie mixed, pls

PurrSent

Board Regular
Joined
Jun 7, 2014
Messages
142
Using Excel 2010

I have spreadsheet, each month on own sheet and, currently, have a Macro to sort the date into ascending order, (day only, ie between 1-28/31) and taking the whole row, of course. See VBA below

Due to other entries being added each month, in addition to those carried forward, there is still the need to sort into date order. If the formula is in the cell, ie "{=Sept!B16}", the date sorting doesn't work, presumably because of seeing the formula and not the date/number.

Is there a way around this so the date can be carried forward from the previous month AND ALSO be sorted by date after new entries have been added?

Any help to achieve this would be very much appreciated.
Many Thanks
%
Code:
Sub Date_Order_1()
  '
  ' Date_Order Macro
  ' Puts Date (Column B) in ascending order
  '
  ' Keyboard Shortcut:
     
     ActiveSheet.Unprotect Password:=""
     
     With ActiveSheet
       .Unprotect
       .Range("A16:BZ115").Sort Key1:=Range("B16"), Order1:=xlAscending
       .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
     End With
        ActiveSheet.Protect Password:=""
  End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I thought I'd just 'Bump' this post.

Also, to clarify a bit, the next two columns, ie C and D, are set to 'carry forward' the details in the previous month, as they will repeat monthly. In this situation, the VBA to sort the date, as given in original post, works well.

It would be useful to be able to carry forward the dates too, but that's when the Date Sorting goes awry. It looks like the dates will need to added each month, unless a solution can be found for the above question.

Many thanks for any help
%
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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
Back
Top