Sort the Active page

[Seansr]

I have a macro that copies data from one spreadsheet and creates a new sheet based on a name entered in a input box

The last bit of the macro I need to do is to Filter the newly created active sheet by Column U - Acending

But I am getting Run Time errors



ActiveSheets.Select
Range("B1").Activate
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Range ("A1:BV18"), SortOn:=xlSortOnValues, Order:=Ascending, DataOption:=xlSortNormal
With ActiveSheets.Select
.SetRange Range("A1:BV18")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

 

[VoG]

Try

ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Range ("A1:BV18"), SortOn:=xlSortOnValues, Order:=Ascending, DataOption:=xlSortNormal
    With ActiveSheet
    .SetRange Range("A1:BV18")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply         
End With

 

[Seansr]

That has returned a

Run-time Error '438':
Object doesn't support this property or method

Complaining about

ActiveSheet.Sort.SortFields.Range ("A1:BV18"), SortOn:=xlSortOnValues, Order:=Ascending, DataOption:=xlSortNormal

Regards

Sean

 

[VoG]

Try

ActiveSheet.Sort.SortFields.Add Key:=Range("A1:BV18"), SortOn:=xlSortOnValues, Order:=Ascending, DataOption:=xlSortNormal

 

[Seansr]

This time :

Run-time error '1004':

Application-defined or object-defined error

Sean

 

[VoG]

Is the sheet protected?

Have you tried recording a macro whilst doing this manually?

 

[Seansr]

The sheet is not protected

I have tried to record the macro and it's where I took the main part of my macro from

But when I record the macro it adds in the name of the sheet, but as this can be different each time I wanted it to be the active sheet it was still had focus on

I have allocated a button with the macro assigned on my master worksheet to make things easy for the operators to use

Sean

 

[VoG]

This works for me - note that I used a smaller range to test

Sub Macro2()
'
' Macro2 Macro
'

'
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("B2:B13") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A1:C13")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[Seansr]

Brilliant

all working,

Took a bit of a tweak

Many Thanks

Sean