Q:

.Select doesn't work when accessing a seperate excel file in vba. Why?

I am creating a code that opens another file, performs some action and closes it.  In the file I am opening, there is a function that organizes the data upon closing.
I do not know how to code the filter in VBA, so I recorded a macro and pasted it into my function.  The code works when I run it by itself, but when I call the main function the '.Select' doesn't appear to select the cells/columns, causing a failure.
The first function is from the first workbook, and the second is being called when the first function closes the file.
'*********First Function************
Sub AddDrawing_Button() 'activated by button in worksheet

PN = Sheets("New Drawing").Range("C5").Cells(1, 1).Value        'Part Number,    D
Rev = Sheets("New Drawing").Range("C5").Cells(3, 1).Value       'Revision,       E

Application.ScreenUpdating = False
Workbooks.Open ("C:\Users\Desktop\MasterDataFile.xlsm") 'Finds the file
Workbooks("MasterDataFile").Worksheets("DATA").Activate
t = Sheets("DATA").Range("D65536").End(xlUp).Row + 1 'finds the bottom row + 1

Sheets("DATA").Range("D1").Cells(t, 1).Value = PN               'Part Number,    D
Sheets("DATA").Range("D1").Cells(t, 8).Value = Rev              'Revision,       E

Workbooks("MasterDataFile").Close SaveChanges:=True
'upon closing this file, it jumps to the following code
Application.ScreenUpdating = True

End Sub

'*********Second Function in Second Workbook************

Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ThisWs As Worksheet
Dim value1 As String
Dim value2 As String
Set ThisWs = Workbooks("MasterDataFile").Worksheets("DATA")

t = ThisWs.Range("D65536").End(xlUp).Row 'end
'Application.ScreenUpdating = False

'The following 6 lines creates a new column and populates
'    each row with the part number and revision combined.  
Cells(1, 24) = "Order"
For s = 2 To t
    value1 = Cells(s, 4)
    value2 = Cells(s, 11)
    ThisWs.Cells(s, 24) = value1 + "Rev" + value2
Next s

'The following was generated by recording a macro, and uses
'    the filter to organize the data.  The error is occurring
'    because the columns are not being selected.  Why?
ThisWs.Columns("D:X").Select
Selection.AutoFilter
ThisWs.AutoFilter.Sort.SortFields.Clear
ThisWs.AutoFilter.Sort.SortFields.Add Key:=Range( _
    "X1:X19519"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ThisWs.AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

'This turns off the filter
ThisWs.Range("A1").Select
ThisWs.Columns("D:X").Select
Selection.AutoFilter
ThisWs.Range("A1").Select

'This deletes the generated column after it has been sorted
ThisWs.Columns("X:X").ClearContents
'Application.ScreenUpdating = True

End Sub

Can someone help me understand why the cells are not being selected, with a way to fix it?
Or if all else fails, can someone post a way to filter the columns without selecting anything.
Thank you.

A:

I have rebuilt your function, It is untested and should work, but it is not completely optimised. Everything is handled in the original function and nothing is handled in the OnClose Event.
'*********First Function************
Sub AddDrawing_Button() 'activated by button in worksheet
    Dim wbMasterDataFile as Workbook
    Dim shtData as Worksheet
    Dim t as long
    Dim s as long

    'PN = Sheets("New Drawing").Range("C5").Value        'Part Number,    D
    'Rev = Sheets("New Drawing").Range("C7").Value       'Revision,       E

    Application.ScreenUpdating = False

    set wbMasterDataFile = Workbooks.Open ("C:\Users\Desktop\MasterDataFile.xlsm") 'Finds the file
    set shtData =  wbMasterDataFile.Worksheets("DATA")

    with shtData
        t = .Range("D65536").End(xlUp).Row + 1 'finds the bottom row + 1

        .Range("D1").Cells(t, 1).Value = Sheets("New Drawing").Range("C5").Value                'Part Number,    D
        .Range("K1").Cells(t, 1).Value = Sheets("New Drawing").Range("C7").Value              'Revision,       E

        .Cells(1, 24).Value2 = "Order"

        For s = 2 To t
            .Cells(s, 24) = .Cells(s, 4) + "Rev" + .Cells(s, 11)
        Next s

        .Columns("D:X").AutoFilter
        With .AutoFilter.Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("X1:X19519"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

        .Columns("D:X").AutoFilter

        .Columns("X:X").ClearContents
    End with

    set shtData = nothing

    wbMasterDataFile.Close SaveChanges:=True

    set wbMasterDataFile = Nothing

    'upon closing this file, it jumps to the following code
    Application.ScreenUpdating = True
End Sub

I have also made some more direct references to the ranges that you are using not through the Cell function
I have to run so can't explain more but will edit with more detail later.

