How to copy or move sheets by using VBA code in Excel for Mac (288402)
The information in this article applies to:
- Microsoft Excel 2004 for Mac
- Microsoft Excel X for Mac
- Microsoft Excel 2001 for Mac
- Microsoft Excel 98 Macintosh Edition
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 INFORMATIONMicrosoft 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
: Sample Code to Copy WorksheetsCopy 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
Sample Code to Move WorksheetsMove 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
REFERENCESMicrosoft 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 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)."
Modification Type: | Minor | Last Reviewed: | 10/10/2006 |
---|
Keywords: | kbinfo KB288402 |
---|
|