Hi All,

I have a list full of names with a column beside each name stating their Shift i.e. NS (night shift) and DS (day Shift)

I recorded a macro to filter the list by "DS" and then copy and paste that to a sheet, and then filter by "NS" and copy and paste that to another list.

Im not a big fan of recording macros but alot of people on forums where saying its the handiest way for filtering.

The Problem i am having, is when I run the code 9 times out of 10 it doesnt copy and paste the first section ("DS"), but the "NS" section always does. but every now and then the "DS" does copy and paste. A bit hit an miss

Can anybody have a look at my code and see if they notice anything wrong, or perhaps a better solution.

Sub SheetUpdate()
'
' SheetUpdate Macro
'

Application.Screenupdating = False

Sheets("Histogram").Unprotect "ops4444"
Sheets("DS Sheet").Unprotect "ops9999"
Sheets("NS Sheet").Unprotect "ops9999"

Worksheets("DS Sheet").Range("A2:G40").ClearContents
Worksheets("NS Sheet").Range("A2:G40").ClearContents

ActiveWorkbook.Connections("owssvr").Refresh



    Sheets("Histogram").ListObjects("Table_owssvr").Range.AutoFilter Field:=5, Criteria1 _
        :="DS"
    Range("A2:G100").Select
    Selection.Copy
    Sheets("DS Sheet").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Histogram").Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects("Table_owssvr").Range.AutoFilter Field:=5, Criteria1 _
        :="NS"
    Range("A2:G100").Select
    Selection.Copy
    Sheets("NS Sheet").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Histogram").Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects("Table_owssvr").Range.AutoFilter Field:=5
    Range("A2").Select
    
    Application.Screenupdating = True
    
    Sheets("DS Sheet").Protect "ops9999", True, True
    Sheets("NS Sheet").Protect "ops9999", True, True
    Sheets("Histogram").Protect "ops4444", True, True
    
    MsgBox "Daily Timesheets Updated"
        
End Sub
Thanks in Advance for any replies