Microsoft KB Archive/288402

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

Article ID: 288402

Article Last Modified on 1/31/2007

-

APPLIES TO


 * Microsoft Excel 2004 for Mac
 * Microsoft Excel X for Mac
 * Microsoft Excel 2001 for Mac
 * Microsoft Excel 98 for Macintosh

-



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.



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 Excel hangs when you attempt to copy worksheet in same workbook



Copy a Specific Sheet in the Active Workbook
Sub Copier1 'Replace &quot;Sheet1&quot; with the name of the sheet to be copied. ActiveWorkbook.Sheets(&quot;Sheet1&quot;).Copy _ after:=ActiveWorkbook.Sheets(&quot;Sheet1&quot;) End Sub

Copy a Specific Sheet in the Active Workbook Multiple Times
Sub Copier2 Dim x As Integer x = InputBox(&quot;Enter number of times to copy Sheet1&quot;) For numtimes = 1 To x       'Loop by using x as the index number to make x number copies. 'Replace &quot;Sheet1&quot; with the name of the sheet to be copied. ActiveWorkbook.Sheets(&quot;Sheet1&quot;).Copy _ After:=ActiveWorkbook.Sheets(&quot;Sheet1&quot;) Next End Sub

Copy the ActiveSheet Multiple Times
Sub Copier3 Dim x As Integer x = InputBox(&quot;Enter number of times to copy active sheet&quot;) For numtimes = 1 To x     'Loop by using x as the index number to make x number copies. ActiveWorkbook.ActiveSheet.Copy _ Before:=ActiveWorkbook.Sheets(&quot;Sheet1&quot;) 'Put copies in front of Sheet1. 'Replace &quot;Sheet1&quot; 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

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(&quot;Test.xls&quot;).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(&quot;Test.xls&quot;).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



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 &quot;Copy Method.&quot; 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 &quot;Move Method.&quot; 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 &quot;Copy Method (MS Excel VBA).&quot;

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 &quot;Move Method (MS Excel VBA).&quot;

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 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 &quot;Copy Method (Excel 97).&quot;

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 &quot;Move Method (Excel 97).&quot;

Additional query words: XL2001KB MAC XL2001 XL98 Excel copy code VBA programming automation Visual Basic macro transfer XLX XL2004

Keywords: kbinfo KB288402

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.