Microsoft Office Tutorials and References
In Depth Information
Sub DeleteSheet()
Application.DisplayAlerts = False
Sheets(1).Delete
Application.DisplayAlerts = True
End Sub
Move and Copy Methods
Another handy skill when manipulating workbooks with VBA code is to change the position
of sheets in a workbook, or to move or copy a sheet to a new workbook. One example of
when moving sheets within a workbook would be handy is when you are creating a
workbook that will be used in an annual report or another type of presentation. Because those
types of reports often have strict formatting guidelines, you might end up moving sheets
around to fit the format. You also might need to move or copy a sheet from a workbook into
a summary workbook. For example, if a project manager summarized labor and equipment
expenditures on a sheet with a known name, you could copy that sheet into a target
workbook and use that sheet, not the original, for any processing you needed to do.
Whether you move or copy a sheet depends on whether or not you want to keep the original
sheet. Copying a sheet is a nondestructive operation in that the sheet being copied isn’t
deleted, but moving a sheet does cause the sheet to be deleted from its original location.
The Move and Copy methods let you set the target position of your moved worksheet using
either the Before or the After parameter. (If for some reason you try to use both, you’ll get an
“expected end of line” error.) For example, the following procedure counts the number of
sheets in the existing workbook and moves the sheet named Summary to the end of the
workbook:
Sub MoveToEnd()
Sheets("Summary").Move After:=Sheets(Sheets.Count)
End Sub
If you want to move or copy more than one sheet in a workbook, you can specify an array in
the Sheets collection. For example, the following procedure moves the sheets named Summary
and PivotTable to the beginning of the workbook:
Sheets(Array("Summary", "PivotTable")).Move Before:=Sheets(1)
If you move or copy a sheet without specifying a Before or After parameter, Excel will create a
new workbook where the copied sheet is the only sheet in the workbook, regardless of the
default number of worksheets included in new workbooks.
PageSetup Object
Regardless of whether you work in Excel through the user interface or using VBA code, the
one thing you need to remember is that the data in your workbooks is the most important
element of your workbook. Once you’re sure the data is entered and summarized correctly,
you can focus on how it looks. You’ll learn a lot more about formatting data and worksheets
Search JabSto ::




Custom Search