Copy Method of Worksheet Object VBA

Copy worksheet method 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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


When we use Copy Worksheet method in VBA?

If we want to Copy worksheet from one workbook to another existing workbook or new workbook we will use Copy method of worksheet object. 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 worksheet method in VBA.

VBA Copy Worksheet Method- Syntax

Here is the 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
In the above Syntax Worksheet represents object and Copy represents metod of Worksheet Object.

VBA Copy Worksheet Method –Before: Example 1

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 Method -After: Example 2

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 Method- Instructions

Please follow the below step by step instructions to execute the above mentioned VBA macros or codes:

  1. Open an Excel Worksheet
  2. Press Alt+F11 :This will Open the VBA Editor. Otherwise, you can open it from the Developer Tab
  3. Insert a Module from Insert Menu
  4. Copy the above code for activating worksheet and Paste in the code window(VBA Editor)
  5. Save the file as macro enabled Worksheet
  6. Press ‘F5’ to run it or Keep Pressing ‘F8’ to debug the code line by line and check how the Worksheet copied .
LIMITED TIME OFFER

Leave A Comment


Related pages


sas miner tutorialcolorindex excelcombobox templatedelete worksheet vbaproject tracking excel spreadsheetgantt chart excel download freeeliminating duplicate rows in excelfinding duplicate in excelpassword worksheetbuilding a dashboard in excelvlookup between two sheetssimple excel dashboard templateshow do i unhide rows in excel 2010sort vba excelexcel vba end of columnunmergevba downloadswhat is hlookup in excel with exampleis null vbaunlock excel filedim as workbookexcel vba check boxcosting excel templatevba color numbershow to convert xls file to xmlexcel formula iferrorjoining cells in excelexcel adohow do you unhide rows in excelms excel lookupvba macros for beginnersexcel vba pivot tablesprotect cells excel 2007how to add data validation in excelhow to learn formulas in excelappend excel filesstacked column chart excel 2007basic excel worksheetvba stringpivot table excel vbaremove duplicate values in excel 2010create userform in excel vba using codehow to add developer tab to the ribbon in wordvba sumifexcel delete duplicate entriespassword protect an excel workbookhow to remove excel sheet passwordoffset function in excel 2010excel formula from another sheetexcel isna function2500 excel vba examplesexcel vba controlsremove duplicate entries excelspider web chart excelexcel macro to delete blank rowsvba appunhide tabs in excelhow to write vba code in accesshlookup formulahow to merge multiple columns in excelactivate activexexcel countif functionexcel 2010 slicer tutorialdelete row excel shortcutcapacity planning template xlshow to run sql query in excelmicrosoft office excel 2007 templatesfile exists vbaadvanced excel vba trainingdelete rows in excel vbapassword log template excelexcel find duplicateshow create macro in exceladodb recordset methodslen function in vbaunmerge cells in excelexcel macro password removervba colorvba input box with optionshide column excelexcel worksheet name from cell valueexcel dashboard templates xls