7. ワークシートの追加・削除・コピーと
    セルの操作−その1
 

Excelでは、ブックにワークシートを追加・削除したり、シートを丸ごと他のブックにコピーすることができます。これらの機能は、もちろんVBAを使って、コードから実行することができます。
 

@ ワークシートの追加 書式 使用例
ブックにワークシートを追加するには、WorkseetsコレクションのAddメソッドを使用します。
単純にブックにワークシートを追加するだけならとても簡単で、Worksheetsコレクションオブジェクトを指定してAddメソッドを実行するだけです。
追加したワークシートは、Excelのデフォルトワークシート名である「Sheet1」のように、自動的に連番でワークシート名が付加されます。
また、ワークシートを追加するブックを指定する場合は、Workbooksプロパティでブック名を指定して、Addメソッドを実行します。この場合、ワークシートを追加するブックはExcelによって開かれていなければなりません。

Addメソッドは4つの引数を持ち、これらを使うことでシートを挿入するブック内の位置や枚数を指定することができます。

<Addメソッドの引数>--------------------
Before :指定したワークシートの前に追加する。
After :指定したワークシートの後ろに追加する。
Count :追加するシートの枚数を指定する
Type :シートのタイプを指定する
--------------------------------------

Addメソッドで追加したワークシートは、自動的にアクティブなワークシートになります。
また、Worksheetオブジェクトが追加されることになりますので、Setステートメントを使用してオブジェクト変数に格納することで、Worksheetオブジェクトの各プロパティやメソッドを使用することができます。

Worksheets.Add (Before, After, Count, Type)

【属性】 メソッド
【所属オブジェクト】 Worksheetsコレクション
【操作対象オブジェクト】 Worksheetsコレクション

<記述例>
Worksheets.Add
Worksheets.Add(After:=Worksheets("Sheet2"))
Set NewWS = Worksheets.Add
Worksheets.Add Count:=3

 

<使用例 現在アクティブなブックにワークシートを1枚追加する>
Worksheets.Add

<使用例 ブック名を指定してシートを追加する>
Workbooks("Book1.xls").Worksheets.Add

<使用例 ワークシートSheet2の後ろにシートを追加>
ActiveWorkbook.Worksheets.Add After:=Worksheets("Sheet2")

<使用例 3数のシートを一度に追加する>
Worksheets.Add Count:=3

<応用例 ワークシートを1枚追加しシート名の変更などを行う>
Sub シートの追加とプロパティ操作()
    Dim NewWS As Worksheet

    Set NewWS = Worksheets.Add(After:=Worksheets("Sheet3"))

    With NewWS
        .Name = "追加シート"
        .Columns.ColumnWidth = 20
    End With
End Sub

<応用例 ワークシートを3枚連続で追加しシート名を設定する>
Sub 3枚連続追加しシート名を設定()
    Dim NewWS As Worksheet

    For i = 1 To 3
        Set NewWS = Worksheets.Add(After:=Worksheets("Sheet3"))
        NewWS.Name = "追加シート" & i
    Next
End Sub

 

A ワークシートの削除 書式 使用例
Deleteメソッドを使用すると、指定したワークシートを削除できます。ただし、一度削除したワークシートは二度と復活できませんので、削除前に注意のメッセージボックスが表示されます。
操作対象のオブジェクトは、ActiveSheetプロパティやWorksheetsプロパティなどを使用して、Worksheetオブジェクトを指定します。
存在しないワークシート名を指定してDeleteメソッドを実行すると、エラーになります。

 

object.Delete

【属性】 メソッド
【所属オブジェクト】 Worksheetオブジェクト
【操作対象オブジェクト】 Worksheetオブジェクト

<記述例>
Activesheet.Delete
Worksheets("追加シート").Delete
 

 

<使用例 シート名を指定して削除する>
Worksheets("追加シート").Delete


ループを使って、複数の削除を実行できます。ただし、1枚ワークシートを削除するごとに、注意のメッセージボックスが表示されます。

<応用例 ループでシートを連続削除する>
Sub ループでシートを連続削除()
    Dim WSName As String

    For i = 1 To 3
        WSName = "追加シート" & i
        Worksheets(WSName).Delete
    Next
End Sub


Array関数を使用して、削除するワークシート名を配列にし、Deleteメソッドを実行すれば、1回の操作で複数のワークシートを一括削除できます。
この場合、Deleteメソッドは1回しか実行されませんから、注意のメッセージボックスも1度だけしか表示されません。
この場合、現在選択状態にあるワークシートを把握するのに、WindowオブジェクトのSelectedSheetsプロパティを使用しています。

<応用例 Array関数を使ってワークシートを一括削除する>
Sub Array関数を使った一括削除()
    Worksheets(Array("追加シート3", "追加シート2", "追加シート1")).Select
    ActiveWindow.SelectedSheets.Delete
End Sub

 

B ワークシートのコピー 書式 使用例
Copyメソッドを使用することで、指定したワークシートをコピーすることができます。
操作対象のワークシートを指定しCopyメソッドを実行するのですが、この時2つの引数を使うことができます。これらの引数を使用することで、コピー先をブック内のどの位置に挿入するのかを指定できます。

<Copyメソッドの引数>------------------
Before :指定したシートの前に挿入する
After :指定したシートの後ろに挿入する
-------------------------------------


コピーされたワークシートは、自動的にコピー元のワークシート名に「(2)」と数字が付加されたシート名が付けられます。

また、コピー元とコピー先が違うブックの場合は、それぞれブック名を付けてワークシートを指定します。

Object.Copy(Before, After)

【属性】 メソッド
【所属オブジェクト】 Worksheetオブジェクト
【操作対象オブジェクト】 Worksheetオブジェクト

<記述例>
Worksheets("Sheet1").Copy
Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")

<使用例 ワークシートSheet1をコピーしSheet3の後ろに挿入する>
Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")

<使用例 ブック間のワークシートコピー>
Workbooks("99-03.xls").Worksheets("Sheet1").Copy _
After:=Workbooks("Book4.xls").Worksheets("Sheet2")


引数「Before」「After」を省略すると、新しいブックが作成されてそこにワークシートがコピーされます。

<使用例 新しいブックを作成しそこにワークシートSheet1をコピーする>
Worksheets("Sheet1").Copy
 

 


Worksheetオブジェクトにはたくさんのプロパティやメソッドが用意されていますが、その中にはセルを操作するものがあります。ここでは、知っていると便利なものをいくつか紹介します。
 
C オートフィルタの設定 書式 使用例
ワークシートの指定したセル範囲にオートフィルタを設定するメソッドです。
オートフィルタとは、特定のセル範囲をリストとして、指定した条件の値だけをリスト表示する機能のことです。
Excel VBAでは、WorksheetオブジェクトのAutoFilterメソッドがこれを実行します。
操作対象オブジェクトに、リスト化するセル範囲をRsngeオブジェクトで指定します。この範囲には、表題となる見出しを含みます。そして、AutoFilterメソッドを実行すれば、セルに矢印ボタンが付いたリストが作成されます。

設定したオートフィルタは、矢印ボタンを押すことで、リスト表示する項目を自由に選べるようになります。


オートフィルタを設定したセル範囲。

これらは、AutoFilterメソッドに用意されている4つの引数を使うことで、コードからオートフィルタを操作することができます。

<AutoFilterメソッドの引数>-----------------------------

Field :フィルタの対象となるフィールド番号。オートフィルタを設定したセル範囲の列番号のことで、一番左端が「1」になる

Criteria1 :抽出条件となる文字列を設定する。「"="」で空白セルの抽出、「"<>"」で空白以外のセルを抽出する。省略すると抽出条件はALLとなりすべての値を表示することになる。
引数「Operator」に「xlTop10Items」を指定した場合は、ここには表示する項目数(たとえば "10")を指定する

Operator :あらかじめ設定されている抽出条件を定数でセットする。使用できる定数は次の通り。
xlAnd、 xlBottom10Items、 xlBottom10Percent、
xlOr、 xlTop10Items、 xlTop10Percent

Criteria2 :2番目の抽出条件を設定する。この場合、引数「Operator」に「xlAnd」と「xlOr」を使うことで、「Criteria1」と「 Criteria2」を組み合わせて複合抽出条件を指定できる
--------------------------------------------------------

Object.AutoFilter(Field, Criteria1, Operator, Criteria2)

【属性】 メソッド
【所属オブジェクト】 Worksheet
【操作対象オブジェクト】 Range

<記述例>
Selection.AutoFilter
Worksheets("Sheet1").Range("A5").AutoFilter _

field:=4, Criteria1:="116%"

<使用例 セル範囲「A5:D22」のデータにオートフィルタを設定する>
Sub オートフィルタの設定()
    Worksheets("Sheet1").Range("A5:D22").Select
    Selection.AutoFilter
End Sub

<使用例 第四フィールドの値116%のものだけを表示>
Sub オートフィルタの操作1()
    Worksheets("Sheet1").Range("A5").AutoFilter _
    field:=4, Criteria1:="116%"
End Sub


まず、操作対象のオブジェクトに、オートフィルタを設定したセル範囲の先頭のセル番地を指定します。

Worksheets("Sheet1").Range("A5").AutoFilter

次に、引数「field」に抽出したいデータがある列を、セル範囲の左から数えた数字で指定します。ここでは、「平年比」はセル範囲の4番目の列になりますので、「4」を指定します。

field:=4

そして、引数「Criteria1」に、抽出条件である「116%」を文字列で指定します。

Criteria1:="116%"

これで、「平年比」が「116%」のセルだけがオートフィルタによって抽出され、リスト表示されます。


「平年比116%」の値を抽出条件に設定

 

D すべての値を表示させる−オートフィルタの操作 書式 使用例
一度設定した抽出条件を元にす場合は、ShowAllDataメソッドを実行します。これで、すべての列のリスト表示が「すべて表示」になります。
次の操作例は、オートフィルタを「すべて表示」に戻し、改めてフィールド2に「トップテン」抽出を実行します。

 

Object.ShowAllData

【属性】 メソッド
【所属オブジェクト】 Worksheet
【操作対象オブジェクト】 Worksheet

<記述例>
Worksheets("Sheet1").ShowAllData

 

<使用例 フィールド2に「トップテン」抽出を行う>
Sub オートフィルタの操作2()
    With Worksheets("Sheet1")
        .ShowAllData
        .Range("A5").AutoFilter field:=2, Criteria1:=10, _
        Operator:=xlTop10Items
    End With
End Sub


最初のShowAllDataメソッドで「すべて表示」になります。

With Worksheets("Sheet1")
.ShowAllData


そして、改めて引数「field」に「2」をセットし、「Operator」に「xlTop10Items」をセットしてAutoFilterメソッドを実行すれば、列「合計」の「トップテン」が表示されます。

.Range("A5").AutoFilter field:=2, Criteria1:=10, _
Operator:=xlTop10Items

 

【コラム Array関数】
配列を作成する関数です。

<書式> Array(arglist)

引数「arglist」に、配列にしたいデータを「,」(カンマ)で区切って設定します。
Worksheetsプロパティなど、コレクションの中から複数のオブジェクトを指定する場合などに使用すると有効です。
 

 

著作・制作   瀬戸 遥   2004/04.