Copy worksheet in VBA is used to Copy the worksheet from one location to another location in the same workbook or another new workbook or existing workbook. Where Copy is a method of Worksheet object. Please find the more information about Copy Worksheet(s) in the following chapter. Sometimes we may want to Copy worksheet in the active workbook at the beginning of the worksheet or in between worksheets or at the end of worksheet. According to our requirement we can Copy the worksheets using Copy method of Worksheet object in VBA.
In this Topic:
- VBA Copy Worksheet: Syntax
- VBA Copy Worksheet: Using Before
- VBA Copy Worksheet: Using After
- VBA Copy Worksheet: Before Specified Worksheet
- VBA Copy Worksheet: To New Workbook
- VBA Copy Worksheet: To Specific Workbook
- VBA Copy Worksheet: Instructions
VBA Copy Worksheet: Syntax
Please find the below syntax to Copy Worksheet using VBA.
Sheets(“Worksheet Number”).Copy([Before], [After])
Where
Before: It’s an Optional parameter. The worksheet will be Copied to before the specified worksheet. Then we can’t specify after parameter.
After: It’s an Optional parameter. The worksheet will be Copied to after the specified worksheet. Then we can’t specify after parameter.
Note: If you don’t specify either before or after, Excel will create new workbook that contains the Copied worksheet
VBA Copy Worksheet: Using Before
Please find the below example, It will show you how to Copy the Worksheet to the beginning.
Sub CopySheet_Beginning() Worksheets("Sheet3").Copy Before:=Worksheets(1) End Sub
In the above example we are Copying the Worksheet named ‘Sheet3’ to the beginning of the worksheet. Where ‘1’ represents the Worksheet index number (Nothing but first available sheet in the workbook).
Sub CopySheet_Beginning1() ActiveSheet.Copy Before:=Worksheets(1) End Sub
In the above example we are Copying the active worksheet to the beginning of the worksheet.
VBA Copy Worksheet: Using After
Please find the below example, It will show you how to Copy the Worksheet at the end of the available worksheets.
Sub CopySheet_End() Worksheets("Sheet3").Copy After:=Worksheets(Worksheets.Count) End Sub
In the above example we are copying the Worksheet named ‘Sheet3’ to the end of the worksheet. Where ‘Worksheets.Count’ represents the number of available worksheets in the workbook
Sub CopySheet_End() ActiveSheet.Copy After:=Worksheets(Worksheets.Count) End Sub
In the above example we are Copying the active worksheet to the end of the worksheet.
VBA Copy Worksheet: Before Specified Worksheet
Please find the below example, It will show you how to Copy the Worksheet either before or after the specified worksheet.
Sub CopySheet_Before() Worksheets("Sheet2").Copy Before:=Sheets("Sheet5") End Sub
Please find the above example, we are Copying ‘Sheet2’ to the before ‘Sheet5’.
Sub CopySheet_Before() ActiveSheet.Copy Before:=Sheets("Sheet5") End Sub
In the above example, we are Copying active sheet to the before ‘Sheet5’.
Sub CopySheet_After() Worksheets("Sheet2").Copy After:=Sheets("Sheet5") End Sub
Please find the above example, we are Copying ‘Sheet2’ to the after ‘Sheet5’.
Sub CopySheet_After() ActiveSheet.Copy After:=Sheets("Sheet5") End Sub
In the above example, we are Copying active sheet to the after ‘Sheet5’.
VBA Copy Worksheet: To New Workbook
Please find the below example, It will show you how to Copy the Worksheet named ‘Sheet1’ to new workbook.
Sub CopySheet_NewWorkbook() Sheets("Sheet1").Copy End Sub
Please find the below example, It will Copy the active worksheet to new workbook.
Sub CopySheet_NewWorkbook() ActiveSheet.Copy End Sub
VBA Copy Worksheet: To Specific Workbook
Please find the below example, It will show you how to Copy the Worksheet named ‘Sheet1’ to Specific workbook before ‘Sheet3’.
Sub CopySheet_SpecificWorkbook () Sheets("Sheet1").Copy Before:=Workbooks("YourWorkbookName.xls").Sheets(“Sheet3”) End Sub
Please find the below example, It will Copy the active worksheet to Specific workbook after ‘Sheet3’.
Sub CopySheet_SpecificWorkbook () ActiveSheet.Copy After:=Workbooks("YourWorkbookName.xls"). Sheets(“Sheet3”) End Sub
VBA Copy Worksheet Method- Instructions
Please follow the below step by step instructions to execute the above mentioned VBA macros or codes:
Step 1: Open an Excel Worksheet
Step 2: Press Alt+F11 to Open VBA Editor
Step 3: Insert a Module from Insert Menu
Step 4: Copy the above code for activating a range and Paste in the code window (VBA Editor)
Step 5: Save the file as macro enabled Worksheet
Step 6: Press ‘F5′ to run it or Keep Pressing ‘F8′ to debug the code line by line and have a look how the Worksheet(s) Copying in the workbook.
Dear Mr.P N Rao ! What is the Basic difference b/w a macro and procedure..??
Hi Venkat,
A Marco is a set of statements which generally created for completing repetitive simple tasks ( without programming concepts like data types, logical expressions, conditional statements etc.)
Where as Procedures are written using programming concepts.
Example: In MS Excel, We can use the Macro recorder to automate simple tasks. Application generates simple statements, where you can’t see any logical statements, etc. But, a programmer writes the procedures using programming concepts, which is more reliable and generalized.
Hope this clarifies.
Thanks-PNRao!
hi,
how to copy one excel sheet(matser file) in to another excel work book( which contain 31 sheets) by date basis
I have several sheets in a source workbook and I need to copy each sheets’s data to another workbook which has got the same name as the sheet name in the source workbook. I have workbooks as many as the number of sheets in the source workbook. How to copy the data from sheets to workbooks in sheet2
i have 2 workbooks in a location C:newfolder. I want sheet 1 from wb1, sheet3 from wb2 and place it one after the other in another existing workbook wb3 in the same location when i click a button in wb3. can you help me with a code?