Excel VBA Sorting sheet when workbook is hidden

Question

Hi guys I'm using a sorting code it was working fine until it didn't after a while of reading though the code and testing I found that the reason for the error was because of the select which as far as I know only work when you can select the sheet first with a visible worksheet and here comes my problem I need the code to work with a hidden workbook anybody that can help with getting the code to work with hidden workbook because I only know this code. Thanks!

what the code here does is sorting after B column but it have the row 1 as header

Set tsheet = ThisWorkbook.Sheets("Courses_tee")

    ThisWorkbook.Sheets("Courses_tee").Range("B2").CurrentRegion.Select
    Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

Well I'm very sorry Aganju that I'm not English and don't have any clue where to put them. it must have been so hard for you to read it — L.Frederiksen, Oct 06 '17 at 23:23

score 2 · Accepted Answer · answered Oct 06 '17 at 12:55

Change this:

ThisWorkbook.Sheets("Courses_tee").Range("B2").CurrentRegion.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

To this:

Set tsheet = ThisWorkbook.Sheets("Courses_tee")
tSheet.Range("B2").CurrentRegion.Sort Key1:=Range("B2"), _
    Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

You don't need to, and you shouldn't Select or Activate things when you can operate directly on the objects instead. Select and Activate essentially mimic keystrokes/mouse-clicks/user actions -- you don't need to Select a range in your code if you know what that range is, i.e.:

Range("A1:A100").Select
Selection.Copy Destination:=Range("B1")

Can become simply:

Range("A1:A100").Copy Destination:=Range("B1")

Etc.

More info:

How to avoid using Select in Excel VBA

Hi David thank you for your code and explaining. it is much appreciated. — L.Frederiksen, Oct 06 '17 at 23:24

score 0 · Answer 2 · answered Oct 06 '17 at 12:54

0

You don't need to select

tSheet.Range("B2").CurrentRegion.Select
Selection.Sort Key1:=tSheet.Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

answered Oct 06 '17 at 12:54

Rory

32,730
5
32
35

Excel VBA Sorting sheet when workbook is hidden

2 Answers2