Macro w/filter, copy, paste,& sort isn't working consistently

  • Thread starter Thread starter kildevil
  • Start date Start date
K

kildevil

newbie here. I recorded this macro and cannot get the last bug out.
have stepped thru it & do not see the problem.

I have a list of employees assigned to 3 shifts. My workbook's firs
sheet (WORKING SHEET) lists:
*shift, seniority,names(alpha), hours previously worked, hours worke
this week, total hours. * Range= (A6:F72)
The macro is meant to (1) create a recovery sheet (OT BACKUP), (2
Filter the names by shift, (3) copy & paste (special) the filtere
lists to another sheet (TOTAL OT SIGNUP), (4) finally sort each shift'
list in descending order by the "total hours" (primary) & descendin
order "seniority" (secondary).

All functions but the sorting work properly. Frequently, the sorte
ranges work fine except for the first row. That row sits unsorted.
have named the ranges for sorting. I have also designated the ranges
rather than naming to see if it effect the outcome. It does not.
have verified that when when I selected the Data, Sort, Range that th
bullet for "No Header" is selected.

One last bit of info: If I rerun the macro without any hours appearin
in the "hours worked this week" column, the sort works fine.

I apologize for my wordiness. Below in the MACRO. Please let me than
anyone, in advance for any help.


OT_SHEET_2 Macro
' Macro recorded 2/9/2004 by ddd
'

'
Selection.AutoFilter Field:=1, Criteria1:="2"
ActiveSheet.ShowAllData
Sheets("TOTAL OT SIGN UP").Select
ActiveSheet.Unprotect
Application.Goto Reference:="DAY_SORT_RANGE"
Selection.ClearContents
Application.Goto Reference:="EVE_SORT_RANGE"
Selection.ClearContents
Application.Goto Reference:="MID_SORT_RANGE"
Selection.ClearContents
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("OT BACKUP").Select
ActiveSheet.Unprotect
Range("A6:E72").Select
Selection.ClearContents
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("WORKING SHEET").Select
Range("A6:E72").Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("OT BACKUP").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True
Scenarios:=True
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("WORKING SHEET").Select
Selection.AutoFilter Field:=1, Criteria1:="2"
Range("B6:F72").Select
Selection.Copy
Sheets("TOTAL OT SIGN UP").Select
Range("F3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Sheets("WORKING SHEET").Select
Selection.AutoFilter Field:=1, Criteria1:="1"
ActiveWindow.ScrollRow = 6
Application.CutCopyMode = False
Selection.Copy
Sheets("TOTAL OT SIGN UP").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Sheets("WORKING SHEET").Select
Selection.AutoFilter Field:=1, Criteria1:="3"
Application.CutCopyMode = False
Selection.Copy
Sheets("TOTAL OT SIGN UP").Select
Range("F25").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Range("A3:E42").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("E3"), Order1:=xlDescending
Key2:=Range("A3") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1
MatchCase:= _
False, Orientation:=xlTopToBottom
Range("F3:J23").Select
Selection.Sort Key1:=Range("J3"), Order1:=xlDescending
Key2:=Range("F3") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1
MatchCase:= _
False, Orientation:=xlTopToBottom
Range("F25:J42").Select
Selection.Sort Key1:=Range("J25"), Order1:=xlDescending
Key2:=Range( _
"F25"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1
MatchCase _
:=False, Orientation:=xlTopToBottom
ActiveWindow.ScrollRow = 1
ActiveSheet.Protect DrawingObjects:=True, Contents:=True
Scenarios:=True
Sheets("WORKING SHEET").Select
ActiveSheet.ShowAllData
Range("F6:F72").Select
Selection.Copy
Range("D6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Range("E6:E72").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E6").Select
End Sub
 
Julie D!!!!

Thank you, so much. This seems to have solved the problem. If I ma
pester you just a little bit more. What is the significance o
changing from "Guess" to "No"?

Don
 
glad it worked - this option has to do with whether or rows have a header
row or not, "guess" means just that, you're asking Excel to look at your
list and make a decision as to whether or not it has a header row ... as you
said in your post - it doesn't, so its then safer to use the "no" option.

cheers
JulieD
 
Back
Top