シートを削除する
Worksheets("abc").Delete
Worksheets(3).Delete
' グラフシートを削除する
Charts(2).Delete
シートを追加する
Worksheets.Add
' グラフシートの追加
Charts.Add
' 3番目のシートの後に追加
Worksheets.Add After:=Worksheets(3)
' 5番目のシートの前に追加
Worksheets.Add Before:=Worksheets(5)
' 末尾に追加
Worksheets.Add After:=Worksheets(Worksheets.Count)
' シートを2つ追加する
Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=2
' シートを追加して、シート名も変更する方法の一例
With Worksheets.Add() ' Addの後ろに()をつけると戻り値を返すので、Withが使える
.Name = "追加シート"
End With
シートを移動する
Worksheets("abc").Move After:=Worksheets(Worksheets.Count)
' 別のブックへ移動する
Worksheets("abc").Move Before:=Workbooks("bbb.xlsx").Worksheets(Worksheets.Count)
' 移動先を指定しない場合、新規ブックとして作成される
Worksheets(3).Move
シートをコピーする
Worksheets("abc").Copy After:=Worksheets(Worksheets.Count)
' 別のブックへコピーする
Workbooks("bbb.xlsx").Activate
Workbooks("aaa.xlsx").Worksheets("abc").Copy After:=Workbooks("bbb.xlsx").Worksheets(Worksheets.Count)
' コピー先を指定しない場合、新規ブックとして作成される
Worksheets(3).Copy
シートの表示・非表示
Worksheets(2).Visible = False ' 非表示に
Worksheets(2).Visible = True ' 再表示
Worksheets(2).Visible = xlVeryHidden ' マクロ以外では再表示できないようにする
シート関連のイベント
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' 選択しているセルが変更された時のイベント。引数の"Target"は、選択されたセル
MsgBox Target.Address
End Sub
Private Sub Worksheet_Activate()
' シートがアクティブになった時のイベント
MsgBox "シート「" & ActiveSheet.Name & "」の内容は変更しないでください"
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
' セルの値が変更された時のイベント。引数の"Target"は、値が変更されたセル
With Target
If .Row >= 10 And .Row <= 20 Then
If .Column >= 10 And .Column <= 20 Then ' 変更されたセルがJ10:T20の範囲だった場合
.Interior.ColorIndex = 5
End If
End If
End With
End Sub
※他にも種類あり
オートフィルタのデータ絞込みを解除する
If ActiveSheet.FilterMode = True Then ' オートフィルタでデータの絞込みがされている場合
ActiveSheet.ShowAllData
' データの絞込みを解除。フィルタそのものを解除ではなく、絞込み無しにして全てのデータを表示している状態に
End If
If ActiveSheet.FilterMode = True Then
ActiveSheet.AutoFilterMode = False ' これはフィルタそのものを解除してしまう
End If
オートフィルタでデータを絞り込む
Range("B2").AutoFilter Field:=1, Criteria1:="リンゴ"
' セルB2を含む有効セル範囲で、左から1番目の列を "=リンゴ" の条件でフィルタリングする
' オートフィルタが未設定の場合は設定される
Range("B2").AutoFilter 1, "リンゴ"
' このように省略できる。上と同じ処理になる
Range("B2").AutoFilter 1, "リンゴ"
Range("B2").AutoFilter 2, ">=2000"
' 複数の列に条件を指定。第1列 = "リンゴ" AND 第2列 >=2000 の条件になる
Range("B2").AutoFilter Field:=1, Criteria1:="桃", Operator:=xlOr, Criteria2:="ぶどう"
' 1列に複数の条件を設定。第1列 = "桃" OR 第1列 = "ぶどう" の条件になる。これは省略表記は出来ない
Range("B2").AutoFilter Field:=2, Criteria1:=">=2000", Operator:=xlAnd, Criteria2:="<=15000"
' こちらは第2列の値が 2000以上かつ15000以下 の条件になる
Range("B2").AutoFilter Field:=1, Criteria1:=Array("リンゴ", "ぶどう", "みかん"), Operator:=xlFilterValues
' Excel2007以降限定で、Operator:=xlFilterValues とすることで1列に3つ以上の条件を設定できる。
' Range("B2").AutoFilter Field:=1, Criteria1:="リンゴ", Criteria2:="ぶどう", Criteria3:="みかん", Operator:=xlFilterValues
' ※この表記は不可
Dim array1(3) As String
array1(0) = "リンゴ"
array1(1) = "みかん"
array1(2) = "パイナップル"
array1(3) = "桃"
Range("B2").AutoFilter Field:=1, Criteria1:=array1, Operator:=xlFilterValues
' 3つ以上の条件指定の場合は、配列型ならOKなので上のようにすることもできる
Range("B2").AutoFilter 1, "*ゴ" ' ワイルドカード指定も可能
Range("B2").AutoFilter 1, "???" ' 任意の3文字
Range("B2").AutoFilter 1, "<>" ' これで「空白セルではない」という条件になる
Range("B2").AutoFilter Field:=1, Criteria1:="リンゴ"
Range("B2").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Range("F2")
' データの絞込みで抽出されたセルのみをコピーし、セルF2に貼り付ける(可視セルのみをコピーすることで実現している)
' ※可視セルに限定しなくてもいけるという説もあり。詳しくはリンク3を参照
オートフィルタで「空白セルではない」という条件で絞り込む
Range("B2").AutoFilter Field:=1, Criteria1:="リンゴ"
' これで、「第一列が空白セルではない」という条件になる。
Range("B2").AutoFilter 1, "<>"
' 省略表記も可能
Range("B2").AutoFilter 1, "" ' 「空白セルである」という条件ならこれで
オートフィルタで、日付を指定してデータを絞り込む (かなり面倒な話)
Range("B2").AutoFilter Field:=1, Criteria1:="2017/1/1"
' Excelのバージョンは2016、セルの書式設定は「標準」「日付」「文字列」の場合、この表記で絞込みできる
' ※日付での絞り込みは、エクセルのバージョンやセルの書式設定によって、絞込みの可否や結果が変わるらしい
' 詳しくはリンク先を参照。日付で絞込みは、単純な形式のみにしたほうがよさそう
' ※Excel2007以降限定、セルの書式設定「標準」「日付」の場合、以下のような絞込みが可能
Range("B2").AutoFilter Field:=1, Operator:=xlFilterValues, _
Criteria2:=Array(1, "2017/1/1", 1, "2017/2/3", 1, "2017/3/5")
' Criteria1:= ではなく、 Criteria2:= であることに注意
' 1, "2017/1/1" の1は、「月」を指定している。"2017/1/1"の月なので、2017年の1月のデータを条件にしていることになる(1日は無関係)
' 上の場合は、2017年の1月・2月・3月のデータのみを絞り込むことになる。何日であるかは無視する
Range("B2").AutoFilter Field:=1, Operator:=xlFilterValues, _
Criteria2:=Array(2, "2017/1/1", 2, "2017/2/3", 2, "2017/3/5")
' 2は「日」を指定するので、この場合は"2017/1/1"・"2017/2/3"・"2017/3/5"の3日分のデータのみを絞り込む
Range("B2").AutoFilter Field:=1, Operator:=xlFilterValues, _
Criteria2:=Array(0, "2017/1/1", 0, "2018/2/3", 0, "2019/3/5")
' 0は「年」を指定するので、この場合は2017年・2018年・2019年のデータで絞り込む
オートフィルタで絞り込んだ結果から、特定列の合計･データ数を取得する
' "B2:D21"のセル範囲のデータに、フィルタを設定する前提。B列は「品名」、C列は「売上」、D列は「担当者」、2行目はタイトル行
Range("B2").AutoFilter Field:=1, Criteria1:="リンゴ" ' B列を"リンゴ"で絞込み
Dim result As Long
result = WorksheetFunction.Subtotal(9, Range(Cells(3, 3), Cells(21, 3)))
MsgBox "「リンゴ」の売上の合計は: " & result
' C列の「売上」の合計を求める。9で合計
result = WorksheetFunction.Subtotal(3, Columns(2)) - 1
MsgBox "「リンゴ」のデータ数は: " & result
' 「リンゴ」のデータ数を求める。3でデータ数
' 下の行にデータが無いなら、合計範囲の指定は列全体でもいいが、タイトル行の数も入ってしまうので-1しておく
オートフィルタで絞り込んだ結果から、特定列のセルの背景色等を変更する
オートフィルタが設定されているか、データの絞り込みがされているかを調べる
全てのシートの名前を取得する(グラフシート含む)
Dim ws As Worksheet
Dim var1 As Variant
Dim cs As Chart
For Each ws In Worksheets ' 全ワークシートの名前を取得。グラフシートは含まない
Debug.Print ws.Name
Next ws
For Each var1 In Sheets ' 全シートの名前を取得。グラフシートも含む
Debug.Print var1.Name
Next var1
For Each cs In Charts ' グラフシートのみを対称にするなら、これくらいかな
Debug.Print cs.Name
Next cs
シートのコード名(Sheet1 Sheet2 等の、順に振られるコード)を取得
MsgBox Worksheets("シート").CodeName ' これだけ
複数のシートを選択する
Sheets(Array("ブック", "セル", "設定")).Select
' シート名での指定だが、「WorkSheets(シート名)」の形ではないらしい
' ※リンク先の情報では、シート名ではなくシートのコード名(Sheet1 など)で指定するとなっているが、
' Sheets(Array("Sheet1")).Select としてもエラーになってしまう。バージョンによる? とりあえず2016では上のコードで
選択している全シート名を取得する
Sheets(Array("ブック", "設定", "その他")).Select
' "ブック", "設定", "その他" シートを選択
Dim ws As Worksheet
For Each ws In ActiveWindow.SelectedSheets ' 選択しているシートを取得
Debug.Print ws.Name
Next ws
シート名についての注意
Worksheets.Add(After:=Worksheets(Sheets.Count)).Name = "sheetX" ' シート「sheetX」を追加
Worksheets.Add(After:=Worksheets(Sheets.Count)).Name = "SHEETX"
' シート「SHEETX」を追加。しかし「この名前はすでに使われています」というエラーメッセージが出て追加できない
' これはシート名は、大文字と小文字を区別しないため。"sheetX"と"SHEETX"は同じものと解釈されてしまうので注意
データのソート
' A～D列にデータがあり、1行目はタイトル行とする場合
' ※Excel2007以降で使えるやり方。パラメータの種類･設定値・既定値はリンク先参照
ActiveSheet.Sort.SortFields.Clear
' 現在のソート条件をクリア。これをしないと、現在の条件に新しい条件を追加する形になる
' これを実行すると、手作業でソートをした場合の条件もクリアされた状態になる
ActiveSheet.Sort.SortFields.Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending
' ソート条件設定。D列でソート、セルの値でソート、昇順でソート の条件
' 以下、ソート実行のためのコード
With ActiveSheet.Sort
.SetRange Range("A1:D13") ' データのある範囲
.Header = xlYes ' 1行目をタイトル行とするか。今回はタイトル行と解釈する
.MatchCase = False ' 大文字と小文字を区別するか。文字列でソートならTrueにすべき
.Orientation = xlTopToBottom ' 並べ替えの方向。xlTopToBottom で普通に上下方向に並べ替え
.SortMethod = xlPinYin ' 日本語をふりがなで並べ替え。xlStroke にすると、文字コードで並べ替え
.Apply ' ソートを実行
End With
' 普通に、値・昇順ソートをするなら以下のように
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("A1")
With ActiveSheet.Sort
.SetRange Range("A1:D13") ' タイトル行も含めた、データの範囲を指定
.Apply
End With
' ※Excel2003までの方法
' ソート条件のクリアというものはないらしい。設定しない条件は、以前の条件が引き継がれるので、なるべく設定すべき
Range("A1:D13").Sort _
Key1:=Range("B1"), Key2:=Range("D1"), Header:=xlYes, _
Order1:=xlDescending, Order2:=xlAscending, MatchCase:=True
' データ範囲"A1:D13"、第1キーB列、第2キーD列、1行目をタイトルと解釈、第1キーは降順、第2キーは昇順、大文字小文字は区別 でソート
シートの保護
With ActiveSheet
.Protect ' シートに保護設定
.Unprotect ' シート保護解除
.Protect Password:="pass01" ' シートにパスワード付きで保護設定
.Unprotect Password:="pass01" ' パスワード付きで保護設定されているシートの保護解除
.Protect
' .Cells(1, 1).Value = "ABC" ' 保護しているシートなので、セルの値は変えられない。このコードはエラーになる
.Unprotect
.Protect UserInterfaceOnly:=True ' マクロでの操作を可能にして、保護をかける
.Cells(1, 1).Value = "ABC" ' これでセルの値を変更できる
End With
Workbooks.Open Filename:="aaa.xlsx"
ActiveWorkbook.Worksheets(1).Protect UserInterfaceOnly:=True ' マクロでの操作を可能にして、保護をかける
ActiveWorkbook.Worksheets(1).Cells(1, 1).Value = "ABC" ' これはエラーにならないのだが
ActiveWorkbook.Save
ActiveWorkbook.Close ' 一度閉じて
Workbooks.Open Filename:="aaa.xlsx"
' ActiveWorkbook.Worksheets(1).Cells(1, 1).Value = "ABC"
' もう一度同ブックを開くと、マクロからの操作を受け付けなくなっている(エラーになる)
ActiveWorkbook.Worksheets(1).Protect UserInterfaceOnly:=True ' もう一度マクロでの操作を可能にして、保護をかければOK
ActiveWorkbook.Worksheets(1).Cells(1, 1).Value = "HJK" ' これならエラーにならない
マクロでしかシートを再表示できないようにする
Worksheets.Add After:=Worksheets(Sheets.count)
ActiveSheet.Name = "Hidden"
Worksheets("Hidden").Visible = xlVeryHidden ' マクロからしかシートを再表示できないように設定し、非表示にする
Worksheets("Hidden").Visible = xlSheetVisible ' 再表示する