how can i copy one worksheet in one workbook into another workbook

Question

I have two separate workbooks one named ActiveWorkbook and one named Terminated Workbook. What I want to do is create a copy button on ActiveWorkbook--Templatesheet which will ask the user which sheet to copy and copy into TerminatedWokbook and named the same as the original one.

I have some code like this and since i am very new to excel macro, so of cause it does not work. Thanks

Sub CopytoTernimal()
Dim CopyName As String
CopyName = InputBox("Please enter the name of sheet which will copy to ternimal")

Sheets("CopyName").Copy
Before:=Workbooks("Terminated Employees").Sheets(1)

End Sub

Since CopyName is a var, not a string, use Sheets(CopyName) with no quotes. And put that Before in the same line of the Copy command. — Daniel Möller
Thanks but i get the error "Subcript out of range" on the copy line — Lan Cui
I believe that before is the problem. Be sure the CopyName exists and try without it. — Daniel Möller
I try copy with name and without it but get the same error message. sorry — Lan Cui
Maybe bacause the name of the Worksheet should has.xml . I will try it. — Lan Cui

Daniel Möller Daniel Möller · Accepted Answer · 2013-07-23T15:57:24

Ok, here's the complete code

Dim CopyName As String
CopyName = InputBox("Please enter the name of sheet which will copy to ternimal")

Dim thisSheet As Worksheet

'you must be sure CopyName is typed correctly, or it wont find the sheet
'also be sure the Activeworkbook name is correctly typed. 
Set thisSheet = Workbooks("ActiveWorkbook").Worksheets(CopyName) 

'copy this sheet
thisSheet.Rows.Copy

Dim NewSheet As Worksheet
Set NewSheet = Workbooks("Terminated Employees").Worksheets.Add()
NewSheet.Name = thisSheet.Name
NewSheet.Paste

To make it a button, go to the main excel window, developer tab, and insert an Active X Button. (Use the Active workbook) Then in design mode, double click that button. The click event will be automatically generated, so you put this code inside that sub. After that, disable the design mode (that's in the developer tab in main window as well). When you click the button, code will be invoked.

how can i copy one worksheet in one workbook into another workbook

1 Answers