VBA Macro Copying Worksheet To Other Workbook

[Leadbeater]

Hi all, I have a macro that is no longer working. I had been using this for weeks, no problems at all. All of a sudden, the macro is not working, bringing up a "Code execution has been interrupted" error. I have the option to "continue" on that warning, and if I do the warning pops up 2 more times, again with the continue option. If I hit continue these 3 times, the macro does actually work, but I didn't have to do this in the past and obviously don't want to deal with the hassle. The 3 "continue" debuggers are at the bottom of this. What I want the code to do: I want to take the data off of my current worksheet, named "Josh" in a workbook called "TaskList.xlsm", and copy and paste it to the worksheet (also) named "Josh" in the file New Hire Workflow.xlsx - doesn't have to necessarily be the entire worksheet, just cells A1:Z100 would be sufficient. There are three worksheets in the NewHireWorkflow workbook, and I need to make sure I am only changing the sheet named "Josh". The macro follows. Thanks in advance.

Sub CopyToNewHire()
'Shortcut key is Ctrl+Q
'This macro opens New Hire workflow workbook, copys my entire worksheet and pastes it into my respective sheet in that file

Cells.Select
Selection.Copy
Workbooks.Open Filename:="I:\Personal\YXP\New Hire Workflow.xlsx"
Sheets("Josh").Select
Cells.Select
ActiveSheet.Paste
Windows("TaskList.xlsm").Activate
Range("A2").Select
Application.CutCopyMode = False
Windows("New Hire Workflow.xlsx").Activate
Range("A2").Select
ActiveWorkbook.Save
ActiveWindow.Close

End Sub

The three debug warnings come up on these three entries:
Sheets("Josh").Select
Windows("TaskList.xlsm").Activate
ActiveWindow.Close

 

[dmt32]

Hi,
see if this change to your code helps:

Sub CopyToNewHire()
'Shortcut key is Ctrl+Q
'This macro opens New Hire workflow workbook,
'copys my entire worksheet and pastes it
'into my respective sheet in that file
    Dim wbDest As Workbook
    Dim wsSource As Worksheet
    Set wsSource = ThisWorkbook.Sheets("Josh")
    Set wbDest = Workbooks.Open(Filename:="I:\Personal\YXP\New Hire Workflow.xlsx")
    wsSource.UsedRange.Copy wbDest.Sheets("Josh").Range("A1")
    'close & save
    wbDest.Close True
End Sub

Dave

 

[Leadbeater]

Dave - I received the same warning with your macro, on this line: wsSource.UsedRange.Copy wbDest.Sheets("Josh").Range("A1")