VBA basics
66. Copy / Move / Delete (Copy / Move / Delete)

Excel macro basics and applications, introduction to Excel VBA
last updated:10-18-2019

66. Copy / Move / Delete (Copy / Move / Delete)

Some of this topic was processed by machine translation. Original


Instructions for copying, moving, and deleting sheets.
In macro VBA, it is often used to copy a template sheet and delete unnecessary sheets.


To copy / move a sheet,
Worksheet.Copy method, Worksheet.Move method
Use these methods of the WorkSheet object.
To delete a sheet,
Use Worksheet.Delete method.

Copy / move sheets

To copy a sheet, use the Worksheet.Copy method.
To move a sheet, use the Worksheet.Move method.

Copy sheet
Expression.Copy Before, Afte


Move sheet
Expression.Move Before, After

An expression is a WorkSheet object.
Sheets(index)
Worksheets ("Sheet name")
Object variable (Worksheet type)
Etc.

Before Specify the sheet when inserting the copied sheet in the position immediately before a specific sheet. However, if you specify the argument After, you cannot specify the argument Before.
After Specify a sheet when inserting the copied sheet in the position immediately after a specific sheet. However, if you specify the argument Before, you cannot specify the argument After.

Remarks
If you omit both the Before parameter and the After parameter,
A new book is automatically created and the sheets are copied and moved into the book.
It is the same as selecting a new workbook by “Move or Copy” of the sheet in Excel.

Copy method usage example
Worksheets(1).Copy
A new book with only Worksheets (1) will be created.

Example of using Move method
Sheets("シート名").Move After:=Sheets(Sheets.Count)

Sheets ("sheet name") is moved to the end of the sheet.

Deleting a sheet

To delete a sheet, use the Worksheet.Delete method.
Expression.Delete
Expression is a WorkSheet object
Sheets(index)
Worksheets ("Sheet name")
Object variable (Worksheet type)
Etc.

Example of using the Delete method
Worksheets(1).Delete
The first sheet is deleted.
But,
When the sheet is deleted, the following message is displayed and the macro is interrupted.

VBA macro sheet Copy,Move,Delete

This message is disturbing.
57 times explained in Application properties,
As explained in 57.Application properties,
Application is an object that represents the entire Excel. In other words think of it as Excel itself. Here are some of the properties. The Application properties introduced here are just a few. Application is an object that represents the whole Excel.
Application.DisplayAlerts = False
By inserting this, this message will not appear.
That means
Application.DisplayAlerts = False
Worksheets(1).Delete
Write VBA like this.
Application.DisplayAlerts = False
this is,
Worksheets(1).Delete
If it runs before this, the place is fine anywhere.



Articles with the same theme "VBA basics"

57. Application properties (Speed up VBA, stop warnings, etc.)
58. What is a collection?
59. Collection processing (For Each)
60. Error processing (On Error)
61. "On Error GoTo" and "Exit Sub"
62. "On Error Resume Next" and the Err object
63. Open a book (Open)
64. Close / Save Book (Close, Save, SaveAs)
65. Insert sheet, rename (Add, Name)
66. Copy / Move / Delete (Copy / Move / Delete)
67. General practice question 7



  • >
  • >
  • 66. Copy / Move / Delete (Copy / Move / Delete)

  • If you find this site useful, please share and bookmark.


    I'm going to pay close attention to the description,
    If you have any mistakes or suggestions,「Contact Us」I hope you will let me know.
    Use the posted VBA code at your own risk.
    We are not liable for any damages such as data corruption.




    If you find this site useful, please share and bookmark.
    To the bottom of the text