Focusing on cells without "selecting" them

[ToddHarmon]

Hi all,

I am still fairly new to VBA and have in issue I can not solve. Is there a way to set the focus on a range of cells and have a fuction, like sort, happen without actually selecting that range? I want my macro to run in the background without disturbing what I am doing on my other open spreadsheets. Currently if the wookbook is not open, I get an error, also I want the sort to happen without the screen flashing at me. Here is my code:

Sub Auto_Sort()
'
' Auto_Sort Macro
'


    
    
    Range("A50:C83").Select
    ThisWorkbook.Worksheets("Dashboard").Sort.SortFields.Clear
    ThisWorkbook.Worksheets("Dashboard").Sort.SortFields.Add Key:=Range( _
        "C50:C83"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ThisWorkbook.Worksheets("Dashboard").Sort
        .SetRange Range("A50:C83")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
'Reset Auto_Sort counter
    Application.OnTime Now + TimeValue("00:00:30"), "Auto_sort"
    
    
End Sub

Hope I did that right!

All help is appreciated!

[patel45]

Sub Auto_Sort()
Worksheets("Dashboard").Range("A50:C83").Sort key1:=Range("C50"), order1:=xlDescending, Header:=xlNo, DataOption1:=xlSortNormal
End Sub

[patel45]

Sub Auto_Sort()
ThisWorkbook.Worksheets("Dashboard").Range("A50:C83").Sort key1:=Range("C50"), order1:=xlDescending, Header:=xlNo, DataOption1:=xlSortNormal
End Sub

[patel45]

Sub Auto_Sort()
ThisWorkbook.Worksheets("Dashboard").Range("A50:C83").Sort key1:=Range("C50"), order1:=xlDescending, Header:=xlNo, DataOption1:=xlSortNormal
End Sub

[ToddHarmon]

That helped out some, taught me a cleaner way to code. I am still getting a "run time error-'9' Script out of range" message when another spreadsheet is open and in use. Any way to fix this problem?

Rep for the cleaner code!

[ToddHarmon]

Thank you, I will see if that works for me. I may have been using that command improperly.

[ToddHarmon]

Still doesn't seem to work for me. I am showing all of my code for this project to see if it is something else that is causing the problem. The Update_Link module works like it should, and so does the sort module as long as this spreadsheet is the open one. As soon as I open any other spreadsheet it now gives a stop 400 error.

Sub Update_Link()
'
'Update_Link Macro
'Dim r as starting row, cc as cell row, str1 as file location, str2-11 as cell location, nc as cell location

nc = 6
nc2 = 7
r = 50
str1 = Range("C3") + "Audit Form'!"
str2 = "$G$5"
str3 = "$N$5"
str4 = "$A$" & cc
str5 = "$B$" & cc
str12 = "$CQ$"
str14 = "$J$"

'Establish links to external spreadsheet

    Range("C8") = "=" + str1 + str2
    Range("C9") = "=" + str1 + str3
        
    
        For cc = 10 To 43
         Range("A" & r).Select
         Range("A" & r) = "=" + str1 + str4 & cc
         Range("C" & r) = "=" + str1 + str5 & cc
         r = r + 1
        Next cc
        
        
         Range("F13") = "=" + str1 + str12 & nc
         Range("F15") = "=" + str1 + str12 & nc2
         
        
        
'Initiate Sorting

    Application.OnTime Now + TimeValue("00:00:01"), "ThisWorkbook.Auto_sort"

'Set focus on primary sheet
    Range("A1").Activate
    
End Sub

Sub Auto_Sort()
'
' Auto_Sort Macro
'


ThisWorkbook.Worksheets("Dashboard").Range("A50:C83").Sort key1:=Range("C50"), order1:=xlDescending, Header:=xlNo, DataOption1:=xlSortNormal

    
'Reset Auto_Sort counter
    Application.OnTime Now + TimeValue("00:00:05"), "ThisWorkbook.Auto_sort"
    
 Range("A1").Select
End Sub

I'm completely confused as to why it won't work. All of the code is in the "ThisWorkbook" section in VBA. Do the modules need to be seperated maybe?

[patel45]

Usually I put all macros in Modules, not in ThisWorkbook area.
I put only some event macros in ThisWorkbook area.

[ToddHarmon]

Going to mark this one as solved. Since this program is primarily used as a stand alone, I have added a workaround by disabling the macro by user input. Thanks for the help, it is appreciated!