Excel VBA ワークシート操作

Excel VBA 目次

Book名とSheet名・シート数を取得
指定ブックの全シートの「拡大縮小印刷」の設定を、ページ数に合せて印刷に変更
指定ブック中の全てのシートで、指定列のセルデータの重複をチェック
追い番を付けてシートを追加(シートをコピー)
　・セルに付けた名前を削除する
シートの内容を削除
オートフィルタを設定
オートフィルタした行を削除
指定フォルダの全てのExcelブックを対象に、検索して置換する
ワークシートをオブジェクトにSet

索引

Book名とSheet名・シート数を取得

　マクロを登録したファイルと、別の Excelファイルを処理したいとき、その別の対象ファイルのファイル名やシート名を取得したいことが有ります。
　こんな場合の事例です。InputBox を使ってセルをクリックするだけなので、手入力の必要はありません。

　注：対象の Excel の、いずれかのシートの複数範囲が選択状態になったいると、セル指定できない場合が有ります。
　このときは、前もって範囲選択を解除しておいて下さい。

　ここで使うのは、「InputBox メソッド」で、通常使う、「InputBox 関数」とは引数が異なります。オブジェクトを取得できる点がポイントです。


Option Explicit
Option Base 1

Sub 対象ExcelBook情報の取得()

    Dim 対象シート As Worksheet
    Dim 指定セル As Range
    Dim シート数 As Integer
    Dim シート名(100, 1) As String
    
    ' 処理対象シートを選択して、ファイル名を取得

    On Error GoTo キャンセル
    Set 指定セル = Application.InputBox _
        (prompt:="処理するファイルの、一つのシートの適当なセル、を選択してください" _
        , Title:="処理対象ファイルの指定" _
        , Type:=8)  ' 8は、セル参照 (Range オブジェクト)

    ' 指定セルの Range に対して、.Parent で親オブジェクトWorksheets を､
    ' その.Parent で、Workbooks を、取得します。

    If 指定セル.Parent.Parent.Name = ThisWorkbook.Name Then
        MsgBox "指定されたファイルは、このマクロのファイル自身です。" _
        & Chr(13) & "このままマクロを終了します。"
        Exit Sub
    End If
    
    ' シート名を全て取得
    
    指定セル.Parent.Activate

    ' 作業中のブックのすべてのワークシートの名前を、For～Nextで配列に登録します。
    シート数 = 1
    For Each 対象シート In Worksheets
        シート名(シート数, 1) = 対象シート.Name
        シート数 = シート数 + 1
    Next
    シート数 = シート数 - 1  'Next で、シート数が1多いので引いておく。
    
    ' 結果表示
    
    If シート数 > 1 Then
        MsgBox "For Each を使う" & Chr(13) _
        & "対象ファイル名は、「" & 指定セル.Parent.Parent.Name & "」です。" _
        & Chr(13) _
        & "含まれるシートは、「" & シート名(1, 1) _
        & "」から、「 " & シート名(シート数, 1) & "」までの、" _
        & シート数 & "枚です。"
    Else
        MsgBox "対象ファイル名は、「" & 指定セル.Parent.Parent.Name & "」です。" _
        & Chr(13) _
        & "含まれるシートは、「" & シート名(1, 1) & "」だけです。"
    End If

   ThisWorkbook.Worksheets("sheet1").Activate
   Range("A1").Resize(シート数, 1).Value = シート名



    '別解：シート数の表示
    シート数 = Worksheets.Count
        
    If シート数 > 1 Then
        MsgBox "Count を使う" & Chr(13) _
        & "対象ファイル名は、「" & 指定セル.Parent.Parent.Name & "」です。" _
        & Chr(13) _
        & "含まれるシートは、「" & Worksheets(1).Name _
        & "」から、「 " & Worksheets(シート数).Name & "」までの、" _
        & シート数 & "枚です。"
    Else
        MsgBox "対象ファイル名は、「" & 指定セル.Parent.Parent.Name & "」です。" _
        & Chr(13) _
        & "含まれるシートは、「" & Worksheets(1).Name & "」だけです。"
    End If

キャンセル:
    Exit Sub
End Sub

　解説：
　InputBox メソッドは、マクロに情報を与えるための、ユーザー入力用のダイアログボックスを表示します。表示したダイアログボックスに入力された情報を返します。
　ダイアログボックスには、[OK] ボタンと [キャンセル] ボタンが表示されます。[OK] ボタンをクリックすると、ダイアログボックスに入力した値が返されます。[キャンセル] ボタンをクリックすると、False が返されます。

expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)

　Type　返されるデータの型を指定します。この引数を省略すると、ダイアログボックスは文字列 (テキスト) を返します。次に示す値の中から 1 つまたは複数の値の合計値を指定します。
　たとえば、文字列または数値が入力できるダイアログボックスを作成するときは、引数 Type の値を 1 + 2 に設定します。

値	意味
0	数式
1	数値
2	文字列 (テキスト)
4	論理値 (True または False)
8	セル参照 (Range オブジェクト)
16	#N/A などのエラー値
64	数値配列

　引数 Type に 0 を指定すると、=2*PI()/360 のような数式が、文字列として返されます。数式内に参照があれば、それらの参照は A1 形式の参照として返されます。参照形式を変換するには、ConvertFormula メソッドを使います。
　引数 Type に 8 を指定すると、Range オブジェクトが返されます。返された Range オブジェクトを別の Range オブジェクトに代入するには、次のように Set ステートメントを使う必要があります。

Set myRange = Application.InputBox(prompt := "Sample", type := 8)

　Set ステートメントを使わないと、変数は Range オブジェクト自体ではなく、セル範囲の値として設定されます。

　InputBox メソッドを使ってユーザーに数式を入力させる場合は、入力する数式はユーザーの言語であるため、その数式を Range オブジェクトに割り当てるときに FormulaLocal プロパティを使う必要があります。
　InputBox メソッドは、InputBox 関数とは機能が異なり、有効なユーザー入力のみを選択したり、Excel のオブジェクト、エラー値、数式を扱ったりすることができます。InputBox メソッドを呼び出すには、Application.InputBox のように、対象となるオブジェクトを指定します。対象オブジェクトを省略すると、InputBox 関数が呼び出されてしまうので注意してください。

　InputBox 関数は、文字列型 (String) の値を返します。ダイアログボックスにメッセージとテキストボックスを表示し、文字列が入力されるか、またはボタンがクリックされると、テキストボックスの内容を返します。

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

　Count プロパティは、コレクションに含まれる要素の数を返します。「'別解：シート数の表示」の方では、このプロパティを使って、シート数を直接求めています。

　Worksheets コレクションは、指定されたブックまたは作業中のブックにあるすべての Worksheet オブジェクトの集まりです。各 Worksheet オブジェクトはワークシートを表します。

　Sheets コレクションは、指定されたブックまたは作業中のブックにあるすべてのシートの集まりです。Sheets コレクションには、Worksheet オブジェクトだけでなく、Chart オブジェクト(グラフ)も含まれます。
　単体の Worksheet オブジェクトを取得するには、Worksheets(index) プロパティを使用します。引数 index には、ワークシートのインデックス番号または名前を指定します。

　On Error ステートメントは、エラー処理ルーチンを有効にし、プロシージャ内でのエラー処理ルーチンの位置を指定します。エラー処理ルーチンを無効にするときに使うこともできます。
　On Error ステートメントを使用していない場合に実行時エラーが発生すると、そのエラーは致命的エラーになり、エラーメッセージが表示されてプログラムの実行が停止します。

　On Error ステートメントの形式は次のとおりです。

ステートメント	内容	メモ
On Error GoTo line	引数 line に指定した行から始まるエラー処理ルーチンを有効にします。　引数 line は必ず指定します。引数 line には任意の行ラベルまたは行番号を指定します。実行時エラーが生成されると、ここで設定したエラー処理ルーチンにプログラムの制御が移り、エラー処理ルーチンがアクティブになります。　引数 line に指定する行は、On Error ステートメントと同じプロシージャ内に存在しなければなりません。この制限に従わなければ、コンパイル時エラーが発生します。	エラー処理ルーチンは、Sub プロシージャまたは Function プロシージャではありません。行ラベルまたは行番号で識別されるコードの一部分です。注：On Error GoTo line で跳んだ部分から、通常処理のいずれかの部分に戻るときは、 Resume line もしくは Resume Next を使います。　GoTo 文で戻ると、次の回のエラーで処理が中断してしまいます。
On Error Resume Next	実行時エラーが発生してもプログラムを中断せず、エラーが発生したステートメントの次のステートメントから実行を継続します。　オブジェクトを操作する場合は、On Error GoTo ステートメントではなく、このステートメントを使ってください。	On Error Resume Next ステートメントは、実行時エラーを発生させたステートメントの直後にあるステートメント、または On Error Resume Next ステートメントを含むプロシージャから最後に呼び出しを行った直後のステートメントを使って、実行を継続します。　このステートメントを使って、実行時エラーが生成されても処理を続けることができます。プロシージャ内の他の場所に制御を移動せずに、エラー処理ルーチンをエラーが発生する可能性のある場所に配置できます。　On Error Resume Next ステートメントは、別のプロシージャが呼び出されるとアクティブでなくなるので、そのルーチン内でインラインエラー処理を行う場合は、呼び出される各ルーチン内で On Error Resume Next ステートメントを実行する必要があります。
On Error GoTo 0	現在のプロシージャに含まれる使用可能なエラー処理ルーチンを無効にします。　"使用可能な" エラー処理ルーチンとは、On Error ステートメントによって有効になっている処理ルーチンのことです。	On Error GoTo 0 ステートメントは、現在のプロシージャ内のエラー処理を無効にします。　プロシージャに番号が 0 の行が含まれていても、その行はエラー処理コードの先頭として指定されません。　On Error GoTo 0 ステートメントを指定していない場合、エラー処理ルーチンはプロシージャの終了時に自動的に無効になります。

　"アクティブな" エラー処理ルーチンとは、エラー処理中の使用可能な処理ルーチンのことです。
　エラー処理ルーチンがアクティブになっている間 (エラーの発生と Resume、Exit Sub、Exit Function、または Exit Property ステートメントの間) は、カレントプロシージャのエラー処理ルーチンはエラーを処理できません。制御は呼び出し側のプロシージャに戻ります。
　呼び出し側のプロシージャに使用可能なエラー処理ルーチンがあれば、そのルーチンがエラーを処理するためにアクティブになります。呼び出し側のプロシージャのエラー処理ルーチンもアクティブであれば、使用可能で非アクティブなエラー処理ルーチンが見つかるまで、制御はプロシージャを呼び出したプロシージャへと引き渡されます。
　使用可能で非アクティブなエラー処理ルーチンが見つからなければ、そのエラーは実際に発生した位置で致命的なエラーになります。エラー処理ルーチンがコントロールを呼び出し側のプロシージャに戻すたびに、そのプロシージャがカレントプロシージャになります。
　いずれかのプロシージャ内でエラー処理ルーチンによってエラーが処理されると、Resume ステートメントで指定した時点でカレントプロシージャ内で実行が再開されます。

　エラー処理ルーチンは、Err オブジェクトの Number プロパティを基にエラーの原因を判別します。また、エラー処理ルーチンは他のエラーが発生する前、またはエラーを引き起こすプロシージャが呼び出される前に、関連する Err オブジェクトのプロパティの値をテストまたは保存する必要があります。
　Err オブジェクトのプロパティの値には、最新のエラーだけが反映されます。Err オブジェクトの Number プロパティに関連付けられたエラーメッセージは、Err オブジェクトの Description プロパティに指定されています。

メモ他のオブジェクトを操作しているときに発生したエラーを処理する場合は、On Error GoTo ステートメントよりも On Error Resume Next 構造の方が適しています。
　オブジェクトとの各やり取りの後で Err オブジェクトを調べることにより、どのオブジェクトをコードで操作したのかどうかを確認します。Err オブジェクトの Number プロパティにエラーコードを設定したオブジェクト、および最初にエラーを発生させたオブジェクトを Err オブジェクトの Source プロパティで調べることができます。

　エラーの発生時以外にエラー処理コードが実行されないようにするには、そのエラー処理ルーチンの直前に Exit Sub、Exit Function、または Exit Property のうち、該当するステートメントを配置します。次に例を示します。

Sub InitializeMatrix(Var1, Var2, Var3, Var4)
On Error GoTo エラー処理
. . .

Exit Sub

エラー処理:
. . .
Resume Next

End Sub

　エラーを処理するためのコードは、どこにでも配置できますが、プロシージャの内側になければなりません。
　上の例では、エラー処理コードの前に、 Exit Sub ステートメントを入れて、 End Sub ステートメントの直前に置いています。こうすることにより、エラーではない、通常のプロシージャは、エラー処理の部分を流れないように工夫してあります。

　他のオブジェクトを操作するオブジェクトを作成する場合、他のオブジェクトから未処理のまま返されるエラーを処理する必要があります。このようなエラーを処理できない場合は、Err オブジェクトの Number プロパティを使用して作成したエラーの 1 つにエラーコードを割り当てます。次に、作成したオブジェクトを呼び出しているアプリケーションにエラーを引き渡します。エラーを指定するには、エラーコードを定数 vbObjectError に追加します。たとえば、エラーコードが 1052 であれば、次のように代入します。

Err.Number = vbObjectError + 1052

Resume ステートメントは、エラー処理ルーチンの終了後に、プログラムの実行を再開します。
　Resume ステートメントの構文の形式は次のとおりです。

ステートメント	内容
Resume [0]	エラー処理ルーチンと同じプロシージャ内でエラーが発生した場合、エラーの原因となったステートメントからプログラムの実行が再開されます。呼び出されたプロシージャ内でエラーが発生した場合、エラー処理ルーチンを含むプロシージャが最後に呼び出したステートメントからプログラムの実行が再開されます。
Resume Next	エラー処理ルーチンと同じプロシージャ内でエラーが発生した場合、エラーの原因となったステートメントの次のステートメントからプログラムの実行が再開されます。呼び出されたプロシージャ内でエラーが発生した場合、エラー処理ルーチンを含むプロシージャが最後に呼び出したステートメントの次のステートメント、または On Error Resume Next ステートメントからプログラムの実行が再開されます。
Resume line	引数 line に指定した行からプログラムの実行が再開されます。引数 line は必ず指定します。引数 line には行ラベルまたは行番号を指定します。また、エラー処理ルーチンと同じプロシージャに指定する必要があります。

注：
エラー処理ルーチン以外の場所で Resume ステートメントを使用すると、エラーが発生します。

この種類の目次に戻る↑　索引へ↓　トップページに戻る

指定ブックの全シートの「拡大縮小印刷」の設定を、
ページ数に合せて印刷、に変更

　Excel の表を、印刷するとき、指定ページ数に収めて印刷したいことが有ります。
印刷設定

　こんなときは、印刷の指定で「拡大縮小印刷」の設定を、「次のページ数に合せて印刷(横１だけもしくは、縦１と両方指定)」にします。

　自分の使っているプリンタを前提に、「拡大/縮小」で、1ページに収まるように調節して、比率を、例えば 60% と絶対指定すると、そのシートを、別のプリンタで印刷したときには、プリンタ・ドライバが違うため、ページに収まりきらずに、中途半端な耳がページからはみ出して、4ページも無駄な紙が出力されてしまう場合も発生します。

　横と縦の両方を指定することもできますし、一方だけ指定すると、もう一方は、内容に合せて、自動設定されます。

　下のマクロは、複数のシートからできているExcelブックの印刷設定を、一気に1ページ印刷指定に変更するものです。

　このマクロをダウンロードできます。→print1pageVBA.xls


Sub 拡大縮小印刷を横1縦1ページに設定()

    Dim 対象シート As Worksheet
    Dim 指定セル As Range
    
    ' 処理対象シートを選択して、ファイル名を取得

    On Error GoTo キャンセル
    Set 指定セル = Application.InputBox _
        (prompt:="処理するファイルの、一つのシートの適当なセル、を選択してください" _
        , Title:="処理対象ファイルの指定" _
        , Type:=8)  ' 8は、セル参照 (Range オブジェクト)

    ' 事例では、このマクロは、「print1pageVBA.xls」というファイル名。
    ' 指定セルの Range に対して、.Parent で親オブジェクトWorksheets を､
    ' その.Parent で、Workbooks を、取得します。

    If 指定セル.Parent.Parent.Name = ThisWorkbook.Name Then
        MsgBox "指定されたファイルは、このマクロのファイル自身です。" _
        & Chr(13) & "このままマクロを終了します。"
        Exit Sub
    End If
        
    指定セル.Parent.Activate

    ' 作業中のブックのすべてのシートの印刷設定を、For～Nextで変更します。
    
    For Each 対象シート In Worksheets
        対象シート.Activate
        With ActiveSheet.PageSetup
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
    Next

   Exit Sub
キャンセル:
   MsgBox "シートを認識できませんでした。終了します。"
   End
End Sub

　解説：
　PageSetup オブジェクトは、ページレイアウトの設定全体を表します。PageSetup オブジェクトには、すべてのページ設定属性 (左余白、下余白、用紙サイズなど) が、プロパティとして含まれています。
　こういったオブジェクト／プロパティ(コード)は、「マクロの記録」を使えば、簡単に生成できるので、「こんな使い方ができる」、ということだけを知っておけば、わざわざ覚える必要はないでしょう。

　上の例では、FitToPagesWide プロパティと FitToPagesTall プロパティの設定を有効にするために、Zoom プロパティを False にしています。
　FitToPagesWide プロパティは、ワークシートを印刷するときに、横何ページ分で収めるかを示す値を指定します。
　FitToPagesTall プロパティは、縦何ページ分で収めるかを示す値を指定します。
　FitToPagesTall プロパティに False が設定されているときは、FitToPagesWide プロパティに従ってワークシートの大きさが決められます。つまり、縦方向は、データの行の数に従って、伸縮します。

　次の使用例は、ワークシートの印刷の向きを横に設定し、印刷します。


With Worksheets("Sheet1")
    .PageSetup.Orientation = xlLandscape
    .PrintOut
End With

　複数のプロパティを同時に設定するときは、With ステートメントを使うと便利です。
　次の使用例は、ワークシート 1 のすべての余白を設定します。


With Worksheets(1).PageSetup
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.75)
    .TopMargin = Application.InchesToPoints(1.5)
    .BottomMargin = Application.InchesToPoints(1)
    .HeaderMargin = Application.InchesToPoints(0.5)
    .FooterMargin = Application.InchesToPoints(0.5)
End With

この種類の目次に戻る↑　索引へ↓　トップページに戻る

指定ブック中の全てのシートで、指定列のセルデータの重複をチェック

　セルに、重複をゆるさないキー・データを入力したものを、複数のシートに分けて管理する場合が有ります。
　こんなとき、シート間で、キーの重複登録をしてしまう誤りを、発生しがちです。

　このマクロは、指定した同一列にあるセルの値の重複を、チェックするために作ったものです。
　キーの行が、キー順に並べられていなくても、ブック中のシートが一つでも複数でも、重複チェックできるところが、ミソです。
　重複元のシート名と行番号が、メッセージとして、指定列に出力されます。

　この事例のExcelを、ダウンロードできます。→CheckRepetitionVBA03.xls

　応用ですが、例えば、A列と B列と C列の組合せをキーに、重複を確認したい場合には、以下のようにします。
　右側で空いている列、例えば X列とすると、このセルに、=A1 & B1 & C1 と文字列結合したデータを、Excelの計算式を使って登録します。
　これを全ての行にコピーして、組合せキーのセルを準備します。
　この結合データ列 X を使って、ここのマクロで重複をチェックできます。

追記：
　各シート毎に、シート内で重複チェックするバージョンも作りました。→CheckRepetitionInSheetVBA02.xls

　単一シートのチェックについては、キーのトップと属性の重複を摘出も、参照下さい。


Option Explicit
Option Base 1

Sub 重複チェック()

Dim データ() As String
Dim 最終行 As Integer
Dim 行 As Integer
Dim セルデータ As String
Dim 例外() As String
Dim 例外件数 As Integer
Dim 例外行 As Integer

Dim 対象シート As Worksheet
Dim 対象シート名 As String
Dim 指定セル As Range

Dim 対象列番号 As Integer
Dim コメント表示列番号 As Integer
Dim データ件数 As Integer
Dim 件数MAX As Integer
Dim 判定 As String
Dim 重複件数 As Integer
Dim メッセージ As String


'処理対象の列を読み込む
    ThisWorkbook.Worksheets("Sheet1").Activate
    対象列番号 = Asc(UCase(Range("A11").Value)) - 64
    コメント表示列番号 = Asc(UCase(Range("B11").Value)) - 64
    重複件数 = 0
'例外データを読み込む
    例外件数 = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row - 14
    If 例外件数 > 0 Then
        ReDim 例外(例外件数)
        For 例外行 = 1 To 例外件数
            例外(例外行) = Range("A15").Cells(例外行, 1).Value
        Next 例外行
    End If

'ブックの指定

    ' 処理対象シートを選択して、ファイル名を取得
   On Error GoTo キャンセル

    Set 指定セル = Application.InputBox _
        (prompt:="処理するファイルの、一つのシートの適当なセル、を選択してください" _
        , Title:="処理対象ファイルの指定" _
        , Type:=8)  ' 8は、セル参照 (Range オブジェクト)

    ' 事例では、このマクロは、「CheckRepetitionVBA01.xls」というファイル名。
    ' 指定セルの Range に対して、.Parent で親オブジェクトWorksheets を､
    ' その.Parent で、Workbooks を、取得します。

    If 指定セル.Parent.Parent.Name = ThisWorkbook.Name Then
        MsgBox "指定されたファイルは、このマクロのファイル自身です。" _
        & Chr(13) & "このままマクロを終了します。"
        Exit Sub
    End If
    
    
    指定セル.Parent.Activate

    ' 作業中のブックの、すべてのワークシートから指定列を検索します。
    For Each 対象シート In Worksheets
        対象シート.Activate
        対象シート名 = 対象シート.Name
        
        最終行 = Cells(ActiveSheet.Rows.Count, 対象列番号).End(xlUp).Row

        For 行 = 1 To 最終行
            セルデータ = Cells(行, 対象列番号).Value
            
            If 例外件数 > 0 Then
                For 例外行 = 1 To 例外件数
                    If セルデータ = 例外(例外行) Then
                        GoTo 次の行へ
                    End If
                Next 例外行
            End If
            
            If セルデータ = "" Then
                '空白セルは対象外。何もせずにスキップ。
            ElseIf 件数MAX = 0 Then
                件数MAX = 1
                ReDim Preserve データ(3, 件数MAX)
                データ(1, 件数MAX) = セルデータ
                データ(2, 件数MAX) = 対象シート名
                データ(3, 件数MAX) = 行
            Else
                判定 = ""
                For データ件数 = 1 To 件数MAX
                    If セルデータ = データ(1, データ件数) Then
                        メッセージ = "重複 シート名:" & データ(2, データ件数) & " 行:" & データ(3, データ件数)
                        Cells(行, コメント表示列番号).Value = メッセージ
                        
                        重複件数 = 重複件数 + 1
                        判定 = "重複"
                        Exit For
                    End If
                Next データ件数
                If 判定 = "" Then
                    件数MAX = 件数MAX + 1
                    ReDim Preserve データ(3, 件数MAX)
                     データ(1, 件数MAX) = セルデータ
                     データ(2, 件数MAX) = 対象シート名
                     データ(3, 件数MAX) = 行
                End If
            End If
次の行へ:
        Next 行
        MsgBox 対象シート名 & vbNewLine & "重複件数= " & 重複件数
        重複件数 = 0
    Next
    MsgBox "処理終了しました。"
    Exit Sub
    
キャンセル:
    MsgBox "★　異常終了　★"

End Sub

この種類の目次に戻る↑　索引へ↓　トップページに戻る

追い番を付けてシートを追加(シートをコピー)

　マクロで結果を表示するときに、セルの巾や書式までマクロで書くのは面倒です。
　こんなときは、書式やウインドウ枠など必要な設定をした「テンプレート」のシートを準備して、マクロで、このテンプレートをコピーして、そこに結果を表示させると便利です。
　ここで、マクロで何度もシートを追加する場合に、コピーして追加するシート名が同じだとエラーになってしまいます。
　下の例は、自動で追番を設定するマクロです。

　この事例のExcelを、ダウンロードできます。→ファイルリスト一覧

　ちなみに、
１．単純に最後にシートを追加するときは、下記のようにします。
　Worksheets.Add after:=Worksheets(Worksheets.Count)
２．
　Worksheets("コピー元シート").Copy After:=Worksheets("作業指示シート")
　としても、追番を付けてコピーできます。ただし、こうすると、追加されるシート名は、「コピー元シート(2)」からスタートしてしまいます。


Dim 追加シート名初期 As String
Dim 追加シート名 As String
Dim 重複 As Integer
Dim シート As Worksheet
Dim シート数 As Integer

    追加シート名初期 = "マクロ計算結果"
    追加シート名 = 追加シート名初期

    For 重複 = 1 To 100
    ' 100枚まで追加しても重複しないように追番を設定します。
        For Each シート In Worksheets
            If シート.Name = 追加シート名 Then
                追加シート名 = 追加シート名初期 & "(" & 重複 & ")"
            End If
        Next シート
    Next 重複
    シート数 = Worksheets.Count
    Worksheets("テンプレート").Copy After:=Worksheets(シート数)
    ActiveSheet.Name = 追加シート名

　解説：
　　Copy メソッド
　Chart、Charts、Sheets、Worksheet、および Worksheets オブジェクトの Copy メソッドは、シートをブック内の他の場所にコピーします。

　expression.Copy(Before, After)
　expression　必ず指定します。対象となるオブジェクトへの参照を返すオブジェクト式を指定します。
　Before　省略可能です。バリアント型 (Variant) の値を使用します。コピーしたシートを特定のシートの直前の位置に挿入するときに、そのシートを指定します。ただし、引数 After を指定すると、引数 Before を指定することはできません。
　After　省略可能です。バリアント型 (Variant) の値を使用します。コピーしたシートを特定のシートの直後の位置に挿入するときに、そのシートを指定します。ただし、引数 Before を指定すると、引数 After を指定することはできません。
　上の事例では、シート数を数えて、最後のシートとしてコピー追加しています。

　追記：新規ブックに貼り付けたい場合は、引数を書きません。
　引数 Before と引数 After の両方を省略すると、新規ブックが自動的に作られて、シートはそのブック内に貼り付けられます。
　自動的に作られた Book に、名前を指定する場合は、SaveAs を使います。

　For Each...Next ステートメントは、配列やコレクションの各要素に対して、一連の処理を繰り返し実行するフロー制御記述です。
　Book名とSheet名の取得の例では、Worksheets に含まれる全てのシート名とシート数を取得しています。

　構文
For Each element In group
[statements]
[Exit For]
[statements]

Next [element]

　element　必ず指定します。コレクションや配列の各要素を繰り返す変数を指定します。コレクションの場合、引数 element にはバリアント型 (Variant) 変数、総称オブジェクト型変数、または任意の固有オブジェクト型のオブジェクトの変数を指定できます。また、配列の場合は、引数 element にはバリアント型のみ指定できます。
　group　必ず指定します。オブジェクトコレクション名または配列名 (ユーザー定義型の配列を除く) を指定します。
　statements　省略可能です。引数 group の各メンバに対して実行するステートメントを指定します。

　引数 group　に要素が 1 つでもある場合、For Each ブロックは始まります。ループがいったん開始されると、引数 group の最初の要素に対して、ループ内のすべてのステートメントは実行されます。引数 group の中に要素がある限り、ループ内のステートメントは、各要素に対して実行を続けます。引数 group の中にもう要素がなくなったとき、ループは終了し、Next ステートメントの次のステートメントに実行が移ります。

　ループから抜け出す別の方法として、ループ内に任意の数の Exit For ステートメントを指定することができます。通常、Exit For ステートメントはいくつかの条件を評価した後で使用します。たとえば、If...Then ステートメントを評価した後で、制御をキーワード Next の次のステートメントに直ちに移します。

　For Each...Next ループはネスト (入れ子) 構造にすることができます。つまり、For Each...Next ループの内部に別の For Each...Next ループを入れることができます。ループをネスト (入れ子) 構造にするときは、それぞれの引数 element に別の変数名を指定してください。

　注意：Next ステートメントの引数 element を省略すると、引数 element が指定されているかのように実行を継続します。Next ステートメントが対応する For ステートメントよりも前にあると、エラーが発生します。

　For... Each ステートメントをユーザー定義型の配列に使用することはできません。バリアント型は、ユーザー定義型の配列を持つことができないためです。

この種類の目次に戻る↑　索引へ↓　トップページに戻る

セルに付けた名前を削除する

　上の項目との関連ですが、シートのセルに名前をつけたものを、一つのブックの中でコピーすると、Excel 2010 では、下記のエラーが表示されます。(～ Excel 2003 までは、エラーにならない。)
　名前は、明示的にシートに付けないかぎり、ブックに付いているからです。

　移動またはコピーしようとしている数式またはシートには、移動またはコピー先のワークシートに既にある名前 'A' が含まれています。この名前を使用しますか?
　コピーまたは移動先のシートに定義されている名前を使用する場合は、[はい] をクリックします。
　数式またはワークシートで参照する範囲の名前を変更する場合は、[いいえ] をクリックし、[名前の重複] ダイアログボックスに新しい名前を入力します。

　セルに付けた名前を削除するには、Excel で以下の操作をします。

～Excel 2003
メニューの [挿入] → [名前] → [定義] を選択すると、そのシートに含まれるすべての「名前」が表示されます。

Excel 2007～
メニューの「数式(この数式のタブにある点が、分かりにくいですね)」→「名前の管理」で、そのシートに含まれるすべての「名前」が表示されます。

ここで、すべて削除します。

　マクロを使って、一覧を表示させるには、下のようにします。


Sub セルに付けた名前を抽出()

   Dim 名前 As Name
   Dim 文字列 As String
   ThisWorkbook.Activate
   
   For Each 名前 In Names
      文字列 = 文字列 & vbNewLine & 名前.Name
      文字列 = 文字列 & vbTab & 名前.RefersTo
   Next
   MsgBox 文字列
End Sub

　削除したいときは、下記のようにします。


Sub セルに付けた名前を削除()
   Dim 名前 As Name
   For Each 名前 In Names
     名前.Delete
   Next
End Sub

　解説：
　Name オブジェクトは、セル範囲に付けられている名前を表します。名前は、Database、Print_Area、Auto_Open などのように組み込まれている名前、またはユーザー定義の名前のどちらかです。

　Application、Workbook、および Worksheet オブジェクト

　Name オブジェクトは、Application、Workbook、および Worksheet オブジェクトに含まれている Names コレクションのメンバーです。単体の Name オブジェクトを取得するには、Names(index) プロパティを使用します。引数 index には、定義されている名前のインデックス番号または名前自体を指定します。
　インデックス番号は、コレクション内での名前の順番に対応します。名前の順番は、名前を文字列として昇順に並び替えた順番になります。並び替えでは、英字の大文字と小文字は区別しません。この順番は、[挿入] メニューの [名前] をポイントし、[定義] および [引用] をクリックすると表示されるダイアログボックスの一覧で確認できます。次の使用例は、アプリケーションの Names コレクションの最初の名前のセル参照を表示します。

MsgBox Names(1).RefersTo

　次の使用例は、mySortRange という名前を作業中のブックから削除します。

ActiveWorkbook.Names("mySortRange").Delete

　名前自体の名前を取得したり、設定したりするには、Name プロパティを使用します。次の使用例は、作業中のブックの最初の Name オブジェクトの名前を変更します。

Names(1).Name = "stock_values"

　Range オブジェクト

　オブジェクトには複数の名前を付けることができますが、Range オブジェクトには Names コレクションはありません。セル範囲に付けられている名前のうち、順番の最も早い名前を取得するには、Range オブジェクトの Name プロパティを使用します。次の使用例は、ワークシート 1 のセル範囲 A1:B1 に付けられている最初の名前の Visible プロパティを設定します。

この種類の目次に戻る↑　索引へ↓　トップページに戻る

シートの内容を削除

　ワークシートの内容をすべて消去したい場合には、下記を使います。
　Clear はデータは消えますが、空白行が残ります。Delete だと、空白行もなくなります。
　このため、私の推奨は、④＋⑤です。
　セル範囲の指定方法として、CurrentRegionも参照下さい。

　注：ThisWorkbook を付けないでこのマクロを走らせると、もし他のブックを開いていて、そこに同じシート名のシートが有ると、そちらを削除してしまうので、ご注意を！


Sub シートの内容を削除()
   ThisWorkbook.Worksheets("Sheet1").UsedRange.ClearContents '①対象のシートの使っているセルデータだけを削除
   Stop
   ThisWorkbook.Worksheets("Sheet1").UsedRange.Clear         '②対象のシートの使っているセルを罫線や背景も含めて削除(ClearContentsは不要)
   Stop
   ThisWorkbook.Worksheets("Sheet1").Cells.Clear             '③対象のシートの全セルを罫線や背景も含めて削除
   Stop
   ThisWorkbook.Worksheets("Sheet1").DrawingObjects.Delete   '④対象のシートの図形オブジェクトを削除
   Stop
   ThisWorkbook.Worksheets("Sheet1").Cells.Delete            '⑤対象のシートのデータを削除して、空白行も除去します
   Range("A1").Select
End Sub

　一行目に、タイトル行があって、二行目以降のデータ行だけを削除したい場合には、下のようにします。
　If 文の部分を入れないと、タイトル行も削除されてしまいます。


   ThisWorkbook.Worksheets("対象シート名").Activate
   
   '既存データの2行目以降を行削除する
   
   'J 列(10列目)を基準に、最終行を求める
   最終行 = Cells(ActiveSheet.Rows.Count, 10).End(xlUp).Row
   If 最終行 > 1 Then
      Rows(2 & ":" & 最終行).Delete Shift:=xlUp
   End If

　解説：
　ClearContents メソッドは、選択範囲から、書式設定を残したまま、数式と文字を削除します。また、グラフからグラフの書式を残したままデータ (系列) を削除します。

　expression.ClearContents

　expression 必ず指定します。対象となる ChartArea オブジェクト、または Range オブジェクトを返すオブジェクト式を指定します。

　Clear メソッドを ChartArea オブジェクト、Legend オブジェクト、Range オブジェクトに指定した場合、オブジェクト全体をクリアします。

　expression.Clear

　expression 必ず指定します。上のいずれかのオブジェクトを返すオブジェクト式を指定します。

　DrawingObjectsは、図形の集合を表すコレクションオブジェクトです。DrawingObjectsは、互換用隠しオブジェクトなので、ヘルプでは表示されません。
　シート上のすべての図形を表すDrawingObjectsコレクションは、対象のシートを表すオブジェクトのDrawingObjectsメソッドで取得できます。また、DrawingObjectsメソッドにインデックス（数値または名前）を指定することで、特定の図形を表すRectangleなどのオブジェクトを取得できます。

この種類の目次に戻る↑　索引へ↓　トップページに戻る

オートフィルタを設定

　オートフィルタ (AutoFilter) は、on off のトグルになっています。
　このため、現在シートにオートフィルタが設定されているかどうかを、AutoFilterMode を使ってチェックした後、コードを走らせます。


   '列幅をフィットさせて、フィルタを付ける
   Cells.EntireColumn.AutoFit
   Range("A1").Select
   If Worksheets("ソート作業").AutoFilterMode = False Then
      Selection.AutoFilter
   End If

　解説：
AutoFilterMode プロパティは、True の場合、シートにオートフィルタの下向き矢印を表示します。このプロパティは、FilterMode プロパティの値に依存しません。値の取得および設定が可能です。ブール型 (Boolean) の値を使用します。
　True の場合、下向き矢印を表示します。False の場合、下向き矢印を削除します。ただし、このプロパティに True を設定することはできません。フィルタをリストに適用したり、下向き矢印を表示するには、AutoFilter メソッドを使います。

AutoFilter メソッドは、オートフィルタを使ってリストにフィルタをかけます。バリアント型 (Variant) の値を使用します。
メモ AutoFilter オブジェクトを返すには、Worksheet オブジェクトの AutoFilter プロパティを適用します。

expression.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

expression 必ず指定します。対象となるオブジェクトへの参照を返すオブジェクト式を指定します。

Field 必ず指定します。バリアント型 (Variant) の値を使用します。フィルタの対象となるフィールド番号を整数で指定します。フィールド番号は、リストの左側から始まります。つまり、最も左側にあるフィールドはフィールド番号 1 になります。

Criteria1 省略可能です。バリアント型 (Variant) の値を使用します。抽出条件となる文字列 ("101" など) を指定します。"=" と指定すると、空白セルが抽出され、"<>" と指定すると空白以外のフィールドが抽出されます。この引数を省略すると、抽出条件は All になります。引数 Operator に xlTop10Items が指定されている場合は、引数 Criteria1 に項目数を指定します (たとえば "10")。

Operator 省略可能です。XlAutoFilterOperator クラスの定数を使用します。
使用できる定数は、次に示す XlAutoFilterOperator クラスのいずれかです。
xlAnd (既定値)
xlBottom10Items
xlBottom10Percent
xlOr
xlTop10Items
xlTop10Percent
引数 Criteria1 および Criteria2 で xlAnd と xlOr を使って組み合わせて、複合抽出条件を指定します。

Criteria2 省略可能です。バリアント型 (Variant) の値を使用します。2 番目の抽出条件となる文字列を指定します。引数 Criteria1 および引数 Operator と組み合わせて使い、複合抽出条件を指定します。

VisibleDropDown 省略可能です。バリアント型 (Variant) の値を使用します。True を指定すると、フィルタのフィールドのオートフィルタのドロップダウン矢印を表示します。False を指定すると、フィルタのフィールドにオートフィルタのドロップダウン矢印を非表示にします。既定値は True です。

解説
すべての引数を省略すると、このメソッドは指定したセル範囲でのオートフィルタのドロップダウン矢印を表示します。

次の使用例は、シート 1 のセル A1 から始まるリストの中で、フィールド 1 が "東京" という文字列であるレコードだけを表示します。フィールド 1 のドロップダウン矢印は非表示になります。


Worksheets("Sheet1").Range("A1").AutoFilter field:=1, Criteria1:="東京"
    VisibleDropDown:=False

この種類の目次に戻る↑　索引へ↓　トップページに戻る

オートフィルタした行を削除

　オート・フィルタで絞り込んだ行を、行削除します。
　例えば、下記のテーブルが有ります。

学年

男女

名前

このテーブルを、オート・フィルタで、学年3年の女子だけの行を残して、他の行を削除したい場合、以下のようになります。
　残したい行は、「学年3年」and「女子」なので、削除する行は、「学年3年」以外 or 「女子」以外となります。


    学年 = "3年生"
    男女 = "女"
    
    '対象の学年以外をフィルタ抽出
    Selection.AutoFilter Field:=1, Criteria1:="<>" & 学年 & "*", Operator:=xlAnd
    Selection.AutoFilter Field:=2
    'フィルタ抽出した行を削除
    Rows("2:" & Rows.Count).Delete Shift:=xlUp
    
    '対象の性別以外をフィルタ抽出
    Selection.AutoFilter Field:=1
    Selection.AutoFilter Field:=2, Criteria1:="<>" & 男女 & "*", Operator:=xlAnd
    'フィルタ抽出した行を削除
    Rows("2:" & Rows.Count).Delete Shift:=xlUp

    '削除しなかった行を、すべて表示
    Selection.AutoFilter Field:=1
    Selection.AutoFilter Field:=2

Rows.Count は、最終行の行数を返します。

ワークシートをオブジェクトにSet

　複数のシート間でデータをやり取りするときに、通常は、都度、必要なシートを、前面に表示 (Activate) させます。
　しかし、しばしばシートを切り替えるときは、シートをオブジェクトに登録しておくと、いちいちシートを (Activate) させなくても、オブジェクトを使って直接参照できるので、便利です。


Dim 集計表シート As Worksheet
Set 集計表シート =  ThisWorkbook.Worksheets("Sheet1")    ' オブジェクトへの参照を代入します。

・・・・・コードで使用・・・・
集計表シート.Range("A1").Value = "秘密"
・・・・・コードで使用・・・・

' 用済後、関連付けを解除します。
Set 集計表シート = Nothing        ' オブジェクトを解放します。

「オブジェクト(上の例では、Worksheet オブジェクト)」とは、私の理解は、「予め準備された、変数のかたまり」です。
「オブジェクト」には、予め「プロパティ(属性)」≒「変数」が準備されていて、「オブジェクト」を使うと、複数の「変数」を一括で扱うことができます。
「オブジェクト」に、値の群を代入するときには、Set 命令を使います。

注：「オブジェクト」には、「プロパティ(属性)」以外に、「メソッド(操作)」も予め準備されていて、サブルーチンのように使うことができます。

この種類の目次に戻る↑　索引へ↓　トップページに戻る

Excel VBA ワークシート操作

Book名とSheet名・シート数を取得

指定ブックの全シートの「拡大縮小印刷」の設定を、 ページ数に合せて印刷、に変更

指定ブック中の全てのシートで、指定列のセルデータの重複をチェック

追い番を付けてシートを追加(シートをコピー)

セルに付けた名前を削除する

シートの内容を削除

オートフィルタを設定

オートフィルタした行を削除

ワークシートをオブジェクトにSet

指定ブックの全シートの「拡大縮小印刷」の設定を、
ページ数に合せて印刷、に変更