hobbiton73
02-01-2013, 09:34 AM
Hi, I wonder whether someone may be able to help me please.
I'm using this https://www.box.com/s/etxi9okhhdqs2sh258og spreadsheet to record staff resource. This is just a test template but I can replicate the same problem I'm experiencing with the full version.
In the 'This Workbook' module of the spreadsheet, I use the following code to autosort the "Input" sheet.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableCancelKey = xlDisabled
With Sheets("Input")
If .Range("A5").Value = "" Then Exit Sub
.Range("A5:AG19").Sort Key1:=Range("$A$1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub
You'll see from the example that there are three names which were correctly sorted on closing the workbook.
The problem I have is that if I go back into the file and enter anything on any of the remaining sheets within the workbook I receive the follwoiung error:
The sort reference is not valid. Make sure that it’s within the data you want to sort, and the first Sort By box isn’t the same or blank.
I've been working on this for a few weeks now, and tried a multitude of example code I've found through research I've done, but I just can't solve the problem.
I just wondered whether someone may be able to look at this please and let me know where I'm going wrong.
Many thanks and kind regards
Chris
I'm using this https://www.box.com/s/etxi9okhhdqs2sh258og spreadsheet to record staff resource. This is just a test template but I can replicate the same problem I'm experiencing with the full version.
In the 'This Workbook' module of the spreadsheet, I use the following code to autosort the "Input" sheet.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableCancelKey = xlDisabled
With Sheets("Input")
If .Range("A5").Value = "" Then Exit Sub
.Range("A5:AG19").Sort Key1:=Range("$A$1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub
You'll see from the example that there are three names which were correctly sorted on closing the workbook.
The problem I have is that if I go back into the file and enter anything on any of the remaining sheets within the workbook I receive the follwoiung error:
The sort reference is not valid. Make sure that it’s within the data you want to sort, and the first Sort By box isn’t the same or blank.
I've been working on this for a few weeks now, and tried a multitude of example code I've found through research I've done, but I just can't solve the problem.
I just wondered whether someone may be able to look at this please and let me know where I'm going wrong.
Many thanks and kind regards
Chris