How to copy or move sheets by using VBA code in Excel for Mac

View products that this article applies to.

This article was previously published under Q288402

SUMMARY

You can use Microsoft Visual Basic for Applications (VBA) code to copy or move sheets within a workbook or between workbooks. This is particularly useful if you want to move or copy many sheets, or if you want to make multiple copies of a sheet.

This article provides samples of VBA code to accomplish these tasks.

Back to the top

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. Note In Microsoft Excel 2001 for Macintosh without Microsoft Office 2001 for Mac Service Release 1, you must place the code in a separate workbook from the sheets being copied.

Excel 2001 for Macintosh had a problem when the VBA code that copies the sheets is in the same workbook as the sheets being copied.

This problem is resolved with Service Release 1 for Microsoft Office 2001 for Mac. For additional informationabout this problem in Excel 2001 for Mac, click the following article number to view the article in the Microsoft Knowledge Base:

286266 (http://support.microsoft.com/kb/286266/ ) Excel hangs when you attempt to copy worksheet in same workbook

Back to the top

Sample Code to Copy Worksheets

Copy a Specific Sheet in the Active Workbook

Sub Copier1()
    'Replace "Sheet1" with the name of the sheet to be copied.
    ActiveWorkbook.Sheets("Sheet1").Copy _
       after:=ActiveWorkbook.Sheets("Sheet1")
End Sub

Copy a Specific Sheet in the Active Workbook Multiple Times


Sub Copier2()
    Dim x As Integer
    
    x = InputBox("Enter number of times to copy Sheet1")
    For numtimes = 1 To x
        'Loop by using x as the index number to make x number copies.
        'Replace "Sheet1" with the name of the sheet to be copied.
        ActiveWorkbook.Sheets("Sheet1").Copy _
           After:=ActiveWorkbook.Sheets("Sheet1")
    Next
End Sub

Copy the ActiveSheet Multiple Times

Sub Copier3()
   Dim x As Integer
   
   x = InputBox("Enter number of times to copy active sheet")
   For numtimes = 1 To x
      'Loop by using x as the index number to make x number copies.
      ActiveWorkbook.ActiveSheet.Copy _
         Before:=ActiveWorkbook.Sheets("Sheet1")
         'Put copies in front of Sheet1.
         'Replace "Sheet1" with sheet name that you want.
   Next
End Sub

Copy All the Sheets in a Workbook Once

Sub Copier4()
   Dim x As Integer

   For x = 1 To ActiveWorkbook.Sheets.Count
      'Loop through each of the sheets in the workbook
      'by using x as the sheet index number.
      ActiveWorkbook.Sheets(x).Copy _
         After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
         'Puts all copies after the last existing sheet.
   Next
End Sub

Back to the top

Sample Code to Move Worksheets

Move Active Sheet to a New Position in Workbook

Sub Mover1()
    ActiveSheet.Move _
       After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
       'Moves active sheet to end of active workbook.
End Sub

Move Active Sheet to Another Workbook

Sub Mover2()
    ActiveSheet.Move Before:=Workbooks("Test.xls").Sheets(1)
    'Moves active sheet to beginning of named workbook.
    'Replace Test.xls with the full name of the target workbook you want.
End Sub

Move Multiple Sheets in ActiveWorkbook to Another Workbook

Sub Mover3()
   Dim BkName As String
   Dim NumSht As Integer
   Dim BegSht As Integer

   'Starts with second sheet - replace with index number of starting sheet.
   BegSht = 2
   'Moves two sheets - replace with number of sheets to move.
   NumSht = 2
   BkName = ActiveWorkbook.Name
    
    For x = 1 To NumSht
      'Moves second sheet in source to front of designated workbook.
      Workbooks(BkName).Sheets(BegSht).Move _
         Before:=Workbooks("Test.xls").Sheets(1)
         'In each loop, the next sheet in line becomes indexed as number 2.
      'Replace Test.xls with the full name of the target workbook you want.
    Next
End Sub

Back to the top

REFERENCES

Microsoft Excel X for Mac and later versions

For more information about the Copy method, from the Visual Basic Editor, click Search Microsoft Visual Basic Help on the Help menu, type copy method, click Search, and then click to view "Copy Method." Three topics are listed. Pick the one that applies to worksheets.

For more information about the Move method, from the Visual Basic Editor, click Search Microsoft Visual Basic Help on the Help menu, type move method, click Search, and then click to view "Move Method." Three topics are listed. Pick the one that applies to worksheets.

Excel 2001 for Macintosh

For more information about the Copy method, from the Visual Basic Editor, click the Office Assistant, type copy method, click Search, and then click to view "Copy Method (MS Excel VBA)."

For more information about the Move method, from the Visual Basic Editor, click the Office Assistant, type move method, click Search, and then click to view "Move Method (MS Excel VBA)."

For additional information about problems with copying worksheets that contain code in the same workbook, click the article number below to view the article in the Microsoft Knowledge Base:

286266 (http://support.microsoft.com/kb/286266/EN-US/ ) XL2001: Excel Hangs When You Attempt to Copy Worksheet in Same Workbook

Microsoft Excel 98 Macintosh Edition

For more information about the Copy method, from the Visual Basic Editor, click the Office Assistant, type copy method, click Search, and then click to view "Copy Method (Excel 97)."

For more information about the Move method, from the Visual Basic Editor, click the Office Assistant, type move method, click Search, and then click to view "Move Method (Excel 97)."

Back to the top