Copying Sheet from xlsm file to already existing xlsx file VBA

[EmmatheDancer]

The task the need to perform is basically taking all the register data at a certain point in the day (say 12.30) and extracting the "absent" records, then placing those in a sheet labelled "12.30" in a spreadsheet created earlier where the name of said spreadsheet is based on the date in cell C2 of the data I'm wanting to copy over. Below is the code I've got so far. My problem is that it opens up the spreadsheet in question but then just stops, no errors, it just doesn't do anything else. I'm sure there's a much better way of doing what I want than I have coded but I am new to coding, google is my teacher so I've done my best! Can anyone help please!!!

Sub AWOL12()
'
' AWOL12 Macro
'
' Keyboard Shortcut: Ctrl+Shift+W
'
    Sheets.Add.Name = ("12.30")
    Sheets("Abscences").Select
    Range("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True
    Cells.Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$H$150").AutoFilter Field:=7, Criteria1:="Absent"
    ActiveSheet.Range("$A$1:$H$150").AutoFilter Field:=6, Criteria1:= _
        ">=10:30", Operator:=xlAnd, Criteria2:="<=12:00"
    Selection.Copy
    Sheets("12.30").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("12.30").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("12.30").Sort.SortFields.Add Key:=Range( _
        "A2:A1048451"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("12.30").Sort.SortFields.Add Key:=Range( _
        "B2:B1048451"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("12.30").Sort.SortFields.Add Key:=Range( _
        "F2:F1048451"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("12.30").Sort
        .SetRange Range("A1:H1048451")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A:H").Select
    Selection.Columns.AutoFit
    EffDate = Format(Workbooks("AWOLs & Blanks 2.xlsm").Sheets("12.30").Range("C2").Value, "dd.mm.yyyy")
    Workbooks.Open ("W:\AWOLs\" & EffDate & ".xlsx")
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "12.30"
    Workbooks("AWOLs & Blanks 2.xlsm").Sheets("12.30").Copy After:=Workbooks(EffDate & ".xlsx").Sheets("12.30")
    ActiveSheet.Name = "12.30"
Workbooks("AWOLs & Blanks 2.xlsm").Sheets("12.30").Cells.Clear
Application.DisplayAlerts = False
Workbooks("AWOLs & Blanks 2.xlsm").Sheets("12.30").Delete
Application.DisplayAlerts = True
Workbooks("AWOLs & Blanks 2.xlsm").Close savechanges:=False
End Sub

 

[RoryA]

Change the shortcut key combination to not include the Shift key. There's a bug that causes the code to stop after the Workbooks.Open line.

 

[EmmatheDancer]

Wow thank you!!! Good to know that my code does actually work, it was driving me mad! I now have a problem with the last few lines of code, I want it to close the source workbook without saving it so that when I open it again there is no data in there, ready to start afresh. At the moment it seems to be saving it. Also the line of code clearing and deleting the "12.30" sheet in the source workbook isn't working. Again no error, the code actually finishes and produces the desired result in the destination workbook, it's just the tying up of the loose ends that it doesn't want to do now! Any ideas?

 

[RoryA]

I don't really see the point of clearing the cells or deleting the sheet if you're not saving the workbook anyway. The sheet was added by your code, so if you don't save, it won't be there the next time you open it.

 

[EmmatheDancer]

I thought that but when I close without saving the sheet is still there so I thought it might resolve the problem deleting manually but it made no difference.

 

[RoryA]

I'm not sure how that could be. If the sheet is there when you open up the file, your code would error when it tries to create another sheet with the same name.

 

[EmmatheDancer]

It's a mystery indeed. I seem to have solved the problem by activating the source sheet before asking it to close etc. Thank you so much for your help! Deadline for macro met...phew!