まりふのひと

フィルターの結果を別のシートにコピペする

Excel VBA


■　いきさつ
ある日、「よくわかる Microsoft Excel2010基礎」（FOM出版）の第８章「データベースの利用」の表

を見てた上司が
とりあえず開催日を 2014年に変えて、区分の担当者に配ってくれ。検討させる...
と言ってきた...　とする。

§１．開催日を 2014年に変える

「26行もある。手で修正するのも面倒だなぁ〜」「マクロを作ってみよう」と着手した。

  1. Public Sub 選択範囲の年を2014にする()
  2. Dim mbTitle As String
  3. Dim i As Long, newYear As Long
  4. Dim datDate As Date
  5. Dim strAns As String
  6. 　
  7. mbTitle = "選択範囲の年を2014にする/" & ThisWorkbook.Name
  8. '来年を作る。
  9. newYear = Year(Date) + 1
  10. '新しい年の入力を求める。
  11. Do
  12. strAns = InputBox("新しい年を入力してください。", mbTitle, newYear)
  13. If strAns = "" Then　　　　 'キャンセル時
  14. Exit Sub
  15. ElseIf IsNumeric(strAns) Then
  16. newYear = Val(strAns)
  17. Exit Do
  18. End If
  19. Loop
  20. '選択したセルが日付型であれば、年をnewYearに変える。
  21. '但し、2月29日になると戻せない。
  22. For i = 1 To Selection.Count
  23. If IsDate(Selection(i)) Then
  24. datDate = Selection(i)
  25. Selection(i) = DateSerial(newYear, Month(datDate), Day(datDate))
  26. End If
  27. Next
  28. 　
  29. End Sub

§２．「曜日」があった方がいいなぁ〜

書式設定で表示させるのは簡単だが、土日は色を付けたい...

  1. D列に「曜日」を入れた。
  2. セルD5“=WEEKDAY(C5)”とし、以下はフィルハンドルでコピー。
  3. 書式設定は、ユーザー定義で“aaa”とし、
  4. 条件付き書式で土日は色を付けるようにした。

    案の定、土日があって、色付きで表示された。
参考

曜日を持たない場合は、数式を使用して、書式設定する。しかし Excel 2003以前と変わっており、今一、自信が持てないが、上手くいっているので問題ないのだろう...

§３．担当者シートを作り区分の入力規制を行う

  1. シート「担当者」を作り、
  2. B2〜B4 の名前を“区分”とした。（入力規制の管理を容易にするため）
  3. F列に入力規制を掛ける。
    1. F列を選択する。
    2. ［データ］タブ/データツールGr.の［データの入力規制］をクリック、
    3. ［データの入力規制］をクリックする。
    4. 右図を入力し、
    5. ［OK］
  4. ついでに‥‥ 「区分」に増減が生じた時は、
    1. 「担当者」シートの区分を更新する。
    2. ［数式］タブ/定義された名前Gr.の［名前の管理］をクリックする。
    3. 【名前の管理】ダイアログボックスの
      　(1)名前の「区分」を選択し、(2)参照範囲を変更する。
§４．区分別のシートに貼り付ける
  1. 「開催日」を［昇順］に並べ替える。
    1. ［フィルター］をオンにする。
  2. 「区分」を“就職”で絞り込む。
  3. 選択して‥‥
  4. 指定のシートに貼り付ける。


マクロ化に備え、上記手順のマクロを記録した。今後、課題を整理する予定。

  1. Sub 区分で絞り込んでコピペする()
  2. 　
  3. Range("C5").Select
  4. ActiveWorkbook.Worksheets("開催状況").Sort.SortFields.Clear　　　　'古い並べ替え基準のクリアー
  5. ActiveWorkbook.Worksheets("開催状況").Sort.SortFields.Add _
  6. Key:=Range("C5:C30"), _　　　　'並べ替えの基準セルで必須　
  7. SortOn:=xlSortOnValues, _　　　'セル内のデータで並べ替え
  8. Order:=xlAscending, _　　　　　'昇順
  9. DataOption:=xlSortNormal　　　 '数値と文字列を別々に並べ替える
  10. With ActiveWorkbook.Worksheets("開催状況").Sort
  11. .SetRange Range("B4:K30")
  12. .Header = xlYes
  13. .MatchCase = False
  14. .Orientation = xlTopToBottom
  15. .SortMethod = xlPinYin
  16. .Apply
  17. End With
  18. Range("B5").Select
  19. Selection.AutoFilter
  20. ActiveSheet.Range("$B$4:$K$30").AutoFilter Field:=5, Criteria1:="就職"
  21. Range("B9").Select
  22. Selection.CurrentRegion.Select
  23. Selection.Copy
  24. Sheets("就職").Select
  25. Range("B4").Select
  26. ActiveSheet.Paste
  27. Range("L5").Select
  28. Application.CutCopyMode = False
  29. Selection.AutoFill Destination:=Range("L5:L12")
  30. Range("L5:L12").Select
  31. Range("B5").Select
  32. End Sub


Office Tanaka によれば、Excel2007 以降変わったようだ。
Excel2003 以前を使っている方には参考にならないかも知れないので、とりあえず、これで止めておく...