Macro runs on wrong workbook on close

**Phil_V** · 07-29-2009, 07:38 AM

Try the following instead.

Note that at the beginning I create a reference to to the worksheet in ThisWorkbook. The object 'ThisWorkbook' always represents the workbook that the macro is stored in.
I have also done away with a lot of the select statements as they are unneccasary. You do not need to select a range, and then refer to the selection, you can just refer directly to the range.
For example;

Range("D4:D" & NumRows + 3).Select
    Selection.Copy

becomes:

Range("D4:D" & NumRows + 3).Copy

Here is the code:

Sub Sort()
Dim ws As Worksheet
Application.ScreenUpdating = False

Set ws = ThisWorkbook.Sheets("Summary of tasks") 'Reference the correct sheet in THIS workbook

NumRows = Application.WorksheetFunction.CountA(ws.Range("B4:B65536")) 'Calculate no of tasks

'If no rows to sort, then report error and exit macro
If NumRows = 0 Then
    MsgBox ("No rows to sort!")
    ws.Range("A4").Select
    Exit Sub
End If

'Convert any rogue formulas to values
ws.Range("D4:D" & NumRows + 3).Copy
ws.Range("D4:D" & NumRows + 3).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'Sort and format rows and exit
With ws.Range("A4:D" & NumRows + 3)
    .Sort Key1:=ws.Range("B4"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    .Sort Key1:=ws.Range("D4"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    With .Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 15
    End With
    With .Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 15
    End With
    With .Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 15
    End With
    With .Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 15
    End With
    With .Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 15
    End With
    With .Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 15
    End With
End With
Application.ScreenUpdating = True
ws.Range("A4").Select
End Sub

Incidently I have highlighted the sort lines as I am unsure what you are trying to achieve there?
At the moment it sorts into Ascending order by B4, then it takes that data and resorts it again but this time into Ascending order by D4, as in two SEPERATE sorts where the second one will destroy the results of the first one.
Were you actually wanting to sort by B4, and then have those sorted results sorted by D4 as a secondary sort?

**Cumberland** · 07-29-2009, 08:27 AM

Thanks for your efforts - I'll get to your code refinements in a moment.

Firstly, I've learnt that the sheet doesn't have to be visible on the screen to carry out certain functions via VBA. Interesting!

However, the macro still fails if this is not the active workbook at the time of closing Excel. All works fine until the sort statement and fails with runtime error 1004 (sort reference is not valid). It works fine if this IS the active workbook at the time of closing Excel. You will probably know the reason why this happens better than I do.

However, through experimenting, I have discovered that my original problem can be solved really easily by simply inserting the following line as the first statement in Workbook_BeforeClose:

ThisWorkbook.Activate

So that, when closing Excel, it activates the correct workbook straight away! Eureka! It now works like a dream.

Now onto your code refinements - I have learnt a couple of tricks from you, so a big thanks - I actually wrote the macro about three years ago when in my infancy of VBA programming and refining the code has simply not been high on my priority list, although the two sort statements that you highlighted should, of course, be:

Selection.Sort Key1:=Range("D4"), Order1:=xlAscending, Key2:=Range("B4") _
     , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Thanks again for your help!