VBA Macro Copying Worksheet To Other Workbook

Leadbeater

New Member
Joined
Dec 9, 2011
Messages
2
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi,
see if this change to your code helps:

Code:
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
 
Upvote 0
Dave - I received the same warning with your macro, on this line: wsSource.UsedRange.Copy wbDest.Sheets("Josh").Range("A1")
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top