Private sub worksheet activate question

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Morning,

I currently have the following code which works fine.

Code:
Private Sub Worksheet_Activate()

Application.GoTo Sheets("POSTAGE").Range("A" & Rows.Count).End(xlUp).Offset(1, 0), True
    ActiveWindow.SmallScroll UP:=10


PostageTransferSheet.Show
End Sub

I would like to apply a date sort of column A when the worksheet is opened.

So the first Row/Cell A8 which is the oldest date say 01/05/2015 then as as you go down each row the date will become more recent.

My code is now shown below BUT my sort is new at A8 & oldest at the last current row.
This should be the opposite way.

I also notice that when this code sort has run my data filters which allow me to sort other columns etc are removed.

Code:
Private Sub Worksheet_Activate()

    Dim x As Long
        Application.ScreenUpdating = False
        With Sheets("POSTAGE")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
            .Range("A8:I" & x).Sort Key1:=Range("A8"), Order1:=xlAscending, Header:=xlGuess
    End With
    ActiveWorkbook.Save
       Application.ScreenUpdating = True




Application.GoTo Sheets("POSTAGE").Range("A" & Rows.Count).End(xlUp).Offset(1, 0), True
    ActiveWindow.SmallScroll UP:=10


PostageTransferSheet.Show
End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Try:
Rich (BB code):
Private Sub Worksheet_Activate()

    Dim x As Long
    
    Application.ScreenUpdating = False
    
    With sheets("POSTAGE")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("A8:I" & x).Sort Key1:=Range("A8"), Order1:=xlDescending, header:=xlGuess
        ActiveWorkbook.Save
        Application.Goto .Cells(.Rows.Count, 1).Offset(1), True
    End With
    
    ActiveWindow.SmallScroll Up:=10
    Application.ScreenUpdating = True
    
    PostageTransferSheet.Show
    
End Sub
 
Last edited:
Upvote 0
Hi,
Trying that code a get a run time error 1004
Application defined or object defined error.

When i debug i see this in yellow.
Application.Goto .Cells(.Rows.Count, 1).Offset(1), True
 
Upvote 0
My bad, change that line to:
Rich (BB code):
Application.Goto .Cells(.Rows.Count, 1).End(xlup).Offset(1), True
 
Upvote 0
Now changed and no error message.
Having said that the dates are as follow.

Row 9 31/10/2017

Row 33 26/09/2017

Row 100 16/05/2017

Last Row 02/01/2017

There is no pattern.

I was looking to achieve as so,

ROW 8 01/01/2017

ROW 50 11/06/2017

ROW 345 22/04/2018

ROW 444 20/06/2018

LAST ROW 03/10/2018

Is my supplied just wrong for this as i took it from another sheet which does what i require ?

Thanks
 
Upvote 0
Hi,
My mistake,but dont know if it makes a difference ??

The code i copied it from Sorts A-Z

I am now using it on a sheet where the Sort is numbers as in date form as opposed to name form.

Does that help.

Thanks
 
Upvote 0
Is the VBA question answered/resolved?

For second part, afraid I do not understand what your ask is and your spreadsheet is not visible to look at and understand what you can see/require.
 
Upvote 0
No
It is not resolved.

Column A sort date is out of date order

The date needs to be sorted Old to New.
Old being started at Row 8 cell A
Then the newest being last row with data in,currently cell 646
 
Upvote 0
Take a look at the date order.

4453.jpg


Below is the working code from another sheet which sorts the dates old to new like i require no problem.
I changed the sheet name to POSTAGE and cell A is where the date is and Row 8 is that start

Code:
Private Sub SortDateoldnew_Click()    
    Dim x As Long
    
    Application.ScreenUpdating = False
    
    With Sheets("HONDA LIST")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        .Range("A3:G" & x).Sort Key1:=Range("G4"), Order1:=xlAscending, Header:=xlGuess
        
    End With
                      
    ActiveWorkbook.Save
    
    Application.ScreenUpdating = True
    Sheets("HONDA LIST").Range("A4").Select
    
End Sub
 
Last edited:
Upvote 0
Here is the recorded macro.

Code:
Sub Macro13()'
' Macro13 Macro
'


'
    ActiveWorkbook.Worksheets("POSTAGE").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("POSTAGE").AutoFilter.Sort.SortFields.Add Key:= _
        Range("A7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("POSTAGE").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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