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

Question

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.

[You don't need `Select`](http://stackoverflow.com/q/10714251/11683). If you insist you do, it probably doesn't work because you don't qualify your `Sheets` etc with a workbook. — GSerg, Jan 05 '17 at 20:16
@YowE3K - maybe check here https://stackoverflow.com/questions/38745818/windows-activate-works-on-every-computer-except-one/38748754#38748754 — Tim Williams, Jan 05 '17 at 20:49
@GSerg - Ahh - that would be it - I always display extensions so that I know I am doing. Thanks. — YowE3K, Jan 05 '17 at 20:50
@YowE3K I do too. For some reason I remembered it the other way round (I always included the extension). I also didn't know it depended on that setting. — GSerg, Jan 05 '17 at 20:52
Are you sure that Workbook_BeforeClose is actually running? It must be added in the "ThisWorkbook" code window, not in a separate module. Try putting a breakpoint in that Sub and close the workbook to see if it ever executes. — tlemaster, Jan 05 '17 at 21:29
When I use step into (F8), the code will execute the line: ThisWs.Columns("D:X").Select but it will fail/give error when it tries to execute the following line — Ray, Jan 05 '17 at 21:44
@GSerg That was an excellent idea! Thank you, but it actually didn't solve the problem :( It is very strange. — Ray, Jan 05 '17 at 22:37

score 0 · Accepted Answer · answered Jan 06 '17 at 01:26

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.

Very close! Thank you so much. I haven't used the 'With' statements very much, so this was very informational. I will attach my final code that is almost identical to @Bulfrog but handles a few runtime errors. — Ray, Jan 06 '17 at 15:16

score 0 · Answer 2 · answered Jan 06 '17 at 15:39

Thank you @Bullfrog for providing me the solution to my problem, I am only posting this because I do not want other vba users to get hung up on code that may not run.

Sub AddDrawing_Button()
Dim ThisWb As Workbook, wbMasterDataFile As Workbook
Dim ThisWs As Worksheet, shtData As Worksheet
Dim t As Long, s As Long
Dim value1 As String, value2 As String

Application.ScreenUpdating = False

Set ThisWb = Workbooks("CombinationIndex")
Set ThisWs = ThisWb.Worksheets("New Drawing")

Set wbMasterDataFile = Workbooks.Open("C:\Users\Desktop\MasterDataFile.xlsm")
Set shtData = Workbooks("MasterDataFile").Worksheets("FinalDATA")

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

    .Range("D1").Cells(t, 1).Value = ThisWs.Range("C5").Value  'Part Number,   D
    .Range("D1").Cells(t, 8).Value = ThisWs.Range("C13").Value  'Revision   ,  E

    .Cells(1, 24).value2 = "Order" 'header to new column

    For s = 2 To t
        value1 = .Cells(s, 4)  'originally Bullfrog's code was giving me an error due to a type mismatch
        value2 = .Cells(s, 11) 'I defined a variable above, and filled it so that it was always a string
        .Cells(s, 24) = value1 + "0Rev" + value2 'will use the combined values to sort data by latest rev
    Next s

    .Columns("D:X").AutoFilter 'using the with function to apply the filter
    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 'Turns off the filter

    .Columns("X:X").ClearContents 'deletes the data
End With

Set shtData = Nothing

wbMasterDataFile.Close SaveChanges:=True 'closes the file

Set wbMasterDataFile = Nothing

Application.ScreenUpdating = True
End Sub

thanks for posting the correction, I got sick and never come back to it until today. — Bullfrog, Jan 12 '17 at 23:20

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

2 Answers2