hobbiton73
02-22-2013, 09:53 AM
Hi, I wonder whether someone may be able to help me please.
You'll see from the attached file that I have two worksheets contained within my workbook, one called "Input", the other called "Resource Summary".
Although this is a cut down version of my spreadsheet, basically the "Resource Summary" sheet is just that, it creates a unique list of names from the multiple lines data on the "Input" sheet.
Part of this workbook contains the following code using the 'Workbook close' event.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim EndRow As Long
Sheets("Input").Protect "password", UserInterfaceOnly:=True
With ThisWorkbook.Worksheets("Input")
ActiveWindow.FreezePanes = False
If Sheets("Input").FilterMode = True Then
Sheets("Input").ShowAllData
End If
Cells.EntireRow.Hidden = False
Cells.EntireColumn.Hidden = False
If .Range("B7").Value = "" Then Exit Sub
'find last row of data
EndRow = Range("B7").End(xlDown).Row - 1
.Range("B7:D" & EndRow).Sort Key1:=.Range("B7"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub
The main purpose of this code is to sort the data via column B on my "Input" sheet always excluding the last row which has the value of "Enter your name".
If I open the file and select the "Input" sheet and then close the file, the sort function works perfectly i.e leaving the cell with the value "Enter your name" as the last row.
The problem I have is that if I open the file, and select the "Resource Summary" sheet, and then close the file the "Enter your name" is mistakingly sorted as part of the named list on both the "Resource Summary" and "Input" sheets, and I've no idea why.
I just wondered whether someone may be able to look at this please and let me know where I've gone wrong.
Many thanks and kind regards
Chris
You'll see from the attached file that I have two worksheets contained within my workbook, one called "Input", the other called "Resource Summary".
Although this is a cut down version of my spreadsheet, basically the "Resource Summary" sheet is just that, it creates a unique list of names from the multiple lines data on the "Input" sheet.
Part of this workbook contains the following code using the 'Workbook close' event.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim EndRow As Long
Sheets("Input").Protect "password", UserInterfaceOnly:=True
With ThisWorkbook.Worksheets("Input")
ActiveWindow.FreezePanes = False
If Sheets("Input").FilterMode = True Then
Sheets("Input").ShowAllData
End If
Cells.EntireRow.Hidden = False
Cells.EntireColumn.Hidden = False
If .Range("B7").Value = "" Then Exit Sub
'find last row of data
EndRow = Range("B7").End(xlDown).Row - 1
.Range("B7:D" & EndRow).Sort Key1:=.Range("B7"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub
The main purpose of this code is to sort the data via column B on my "Input" sheet always excluding the last row which has the value of "Enter your name".
If I open the file and select the "Input" sheet and then close the file, the sort function works perfectly i.e leaving the cell with the value "Enter your name" as the last row.
The problem I have is that if I open the file, and select the "Resource Summary" sheet, and then close the file the "Enter your name" is mistakingly sorted as part of the named list on both the "Resource Summary" and "Input" sheets, and I've no idea why.
I just wondered whether someone may be able to look at this please and let me know where I've gone wrong.
Many thanks and kind regards
Chris