Having to Lose Focus from Main worksheet sheet To Execute Code on second worksheet to avoid error

[Ark68]

Consider this code:

Sub file_listing()
     With Worksheets("DYNAMIC")
                .Activate
                .Range("A2:A" & i + 1).Sort key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess
     End With
End Sub

This code is executed with a button push on sheet1 of the workbook. I'd prefer to keep sheet1 in focus while this code is being executed. The only way I can run this code without running into an error ("The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank") , is to add the ".activate". But when I do that, the focus leaves sheet1 to go to worksheet "Dynamic". I don't want this.

 

[AlphaFrog]

Try this.

[COLOR=darkblue]Sub[/COLOR] file_listing()
     [COLOR=darkblue]With[/COLOR] Worksheets("DYNAMIC")
        [SIZE=3][B][COLOR=#ff0000].[/COLOR][/B][/SIZE]Range("A2:A" & i + 1).Sort key1:=[SIZE=3][B][COLOR=#ff0000].[/COLOR][/B][/SIZE]Range("A2"), Order1:=xlDescending, Header:=xlGuess
     [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Make sure there is a period in front of both ranges.
.Range("A2:A" & i + 1)
.Range("A2")


Or this.

Sub file_listing()
    [B]Worksheets("DYNAMIC")[/B].Range("A2:A" & i + 1).Sort Key1:=[B]Worksheets("DYNAMIC")[/B].Range("A2"), Order1:=xlDescending, Header:=xlGuess
End Sub

 

[Ark68]

Awesome. Thank Alpha. Something as small as a "." has so much influence on success of a code.

 

[AlphaFrog]

You're welcome. Thanks for the feedback.