エクセルのマクロについて（オートフィルタ＆シート分け等）

質問

管轄　担当者　店名　販売数
東京　佐藤　 ●●店　100
東京　佐藤　 ××店　120
東京　佐藤　 △●店　 50
大阪　山本　 ▲△店　 83
大阪　山本　 △▲店　 15
大阪　山本　 ●△店　 30
福岡　川田　 ◎×店　 68
福岡　川田　 □×店　 24

このようなデータ(約300行)がたくさんあります。
オートフィルタで各担当者(約20名)に分け、各担当者のシートを自動作成し、担当者の列を削除したいのですが、同じファイルにシートを追加する方法でよろしいのですが・・・

マクロ初心者でして、わからないことが多いです。
どなたかご存知の方お教え下さい。どうぞよろしくお願いします。

回答No.2

matsu_jun · ベストアンサー

以下を、VBAの標準モジュールに貼り付けて実行してみてください。
「管轄」がA列、「担当者」がB列、「店名」がC列、「販売数」がD列に入っているものとします。元のデータが入っているシート名を「元データ」としています。

'---ここから---

Sub 担当別シート作成()
Application.ScreenUpdating = False
For i# = 2 To Worksheets("元データ").Cells(2, 2).End(xlDown).Row
j# = 1
'検索中の人のシートが既にできているかを判断する。
For Each sheet_name In Worksheets
If sheet_name.Name = Worksheets("元データ").Cells(i, 2).Value Then
j = 2
Exit For
End If
Next
'検索中の人のシートがない場合、新規に作成する。
If j = 1 Then
Worksheets.Add After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = Worksheets("元データ").Cells(i, 2).Value
For j = 1 To 4
Worksheets(Worksheets.Count).Cells(1, j).Value = Worksheets("元データ").Cells(1, j).Value
Next j
End If
'データのコピー
For j = 4 To 1 Step -1
Worksheets(Worksheets("元データ").Cells(i, 2).Value). _
Cells(Worksheets(Worksheets("元データ").Cells(i, 2).Value). _
Cells(65535, 1).End(xlUp).Row + 1, j).Value = Worksheets("元データ").Cells(i, j).Value
Next j
Next i
'それぞれのシートの列幅を最適化します。
For Each sheet_name In Worksheets
sheet_name.Columns("A:D").AutoFit
Next
'元データのB列(担当者)を消します。
Worksheets("元データ").Columns("B:B").Delete
Application.ScreenUpdating = True
End Sub

'---ここまで---

「標準モジュールに貼り付ける」方法がわからないときは、以下の方法でお試しを。

1) ツール－マクロ－マクロの記録 で出てきたウィンドウの、「マクロ名(M)」と書かれた欄に、「担当別シート作成」と書き込んで、「OK」をクリックしてください。
2) 何も操作せずに、マクロの記録を終了してください。
3) AltキーとF8キーを同時に押して「マクロ」ウィンドウを開きます。
4) 「担当別シート作成」を選択し、右の「編集(E)」ボタンをクリックしてください。
5) 「Microsoft Visual Basic」というウィンドウが新たに立ち上がり、
　Sub 担当別シート作成()
　　'
　　' 担当別シート作成 Macro
　　' マクロ記録日 : 2005/2/15 ユーザー名 : ○○ ○○
　　'

　　'
　End Sub
と表示されているはずです。
6)このSub 担当別シート作成()と書かれている行と、End Subと書かれている行の間に、上のソースをコピーして貼り付けます。
7) Microsoft Visual Basicウィンドウを閉じます。
8) 後はマクロを実行するのみ。

ただし上のマクロは「担当者」列を消去する上に、「元に戻す」コマンドで復活しないので、必ずバックアップを取ってから行ってください。

お礼

お礼が遅くなりましたが、とても丁寧にお教え下さってありがとうございました。
実際にやってみました。Ｎｏ．４のお答えの自動記録の方は日頃使っているので大丈夫なのですが、実際ＶＢＡを一から作るとなると難しくわかりません。大変助かりました。
申し訳ないのですが、再度新しい質問が発生しましたので、よろしければまたご回答よろしくお願いします。

回答No.4

matsu_jun

#2のmatsu_junです。
#3に対して回答いたしますが、質問者のmiechin様にもお役に立つことができたら幸いです。
#2の下の部分と合わせてお読みください。

Excelのマクロとは、Excelの操作手順を憶えさせ、次回から同じ手順の操作を自動的に実行する機能だということはご存知でしょうか？
ということは、Excelのメニューの中にマクロの記録、すなわち操作手順を憶えさせる動作を開始するコマンドと、マクロの再生、すなわち記憶した動作を再生させるコマンドがあるはずですよね。
前者の「マクロの記憶」はExcelのメニュー(画面上段左から、ファイル(F) 、編集(E)、表示(V)・・・と書かれている部分です)から、「ツール(T)－マクロ(M)－新しいマクロの記録(R)」で呼び出すことができ、後者の「マクロの再生」は同じくExcelのメニューから、「ツール(T)－マクロ(M)－マクロ(M)」で呼び出すことができるわけです。(後者は「Alt」キーを押しながら「F8」キーを押してもOKです)
ちなみにExcel2003などでは使っていないメニューが隠れてしまっているので、メニューを開いた時の一番下の矢印をクリックしてくださいね。

さて、ツール(T)－マクロ(M)－新しいマクロの記録(R) を選んだら、「マクロの記録」という名前の小さなウィンドウが表示されませんでしょうか？そこで最初の設定をして、「OK」をクリックすると記録が始まります。つまり、その後の操作をExcelがどんどん憶えていくわけです。
マクロは何種類でも記憶できますので、ここで、後から分かりやすいようにマクロ名(M)を設定するわけです。マクロ名は、何もしない状態では「Macro1」となっています。別にこのままで問題があるわけではないのですが、後から判別できるように名前をつけます。そのほかの設定項目は今のところ何かを知る必要はありません。ここを設定・変更すると後から別のことで困ることが発生することがありますので、ある程度慣れた人向けのものです。

で、「OK」をクリックすると、いよいよマクロの記録が始まります。と同時に、■と何か表っぽいアイコンの2つのボタンだけがある小さなウィンドウが画面上に表示されますね。このうち左側の■がが記録の終了をさせるときのボタンになります。これをクリックするまでの作業が記録されます。

試しに記録を開始した状態で今開いているシートのセルA1に「3」という数字を入力してみて、マクロを終了しましょう。セルA3に合わせてマウスをクリックし、3を入力、Enterキーを押したら、上で説明した■ボタンをクリックしてください。

これで、「今開いているシートのセルA1に3を入力する」マクロの完成です。

一度A1のセルの中身を消去して、メニューの「ツール(T)－マクロ(M)－マクロ(M)」を選択して表示される「マクロ」というウインドウから、先ほど作ったマクロを選択して、右側の「実行(R)」ボタンをクリックしてみてください。自動的にA3のセルに3が入力されましたか？

できましたら先に進みましょう。
「今開いているシートのセルA3を選択し、そこに3という数字を入力する」という操作を、人間(とはいっても英語を理解する人)とパソコンが共に理解できるように記述したのがVBAなのです。試しに上で登録したものがVBAではどのように表現されているのかを見てみましょう。

Excelのメニューから「ツール(T)－マクロ(M)－マクロ(M)」を選択し、「マクロ」ウィンドウを表示させます。先ほど作ったマクロ(Macro1)を選択して、今度は右側の「編集(E)」をクリックしてください。すると、画面右側の白い部分に以下のように表示されませんか？

Sub Macro1()
'
' Macro1 Macro
' マクロ記録日 : 2005/2/20 ユーザー名 : ○○○
'

'
Range("A1").Select
ActiveCell.FormulaR1C1 = "3"
Range("A2").Select
End Sub

○○○の部分には貴方の名前が入っていると思います。これについて1行ずつ内容の説明をしていきます。

1行目の「Sub Macro1()」というのは、ここからMacro1という名前のマクロの動作説明を行いますよという宣言行と考えます。行頭の「Sub」や、行末の「()」というのは、VBAをより深く勉強する中で自然と意味の理解できるものですが、今のところはお約束でつくものだと考えてください。
2行目以下、しばらく「'」で始まります。これをコメントと呼び、「この行についてここから後ろはコンピュータは理解する必要はありませんよ」という記号です。だから、人間が後から参照した時、何のことについて書いたかとか、いつこれを記録したとか、そういう覚え書きみたいなことを記述するためのものです。実際4行目には、何年何月何日に誰が記録したという、マクロを再生する上でどうでもいい記述がなされていますよね。
8行目「Range("A1").Select」には、「セルA1を選択(select)する」と書かれています。例えばA列全体を選択していればこの行には「Range("A:A").Select」と書かれますし、セルA1からE5までをドラッグしていた場合は「Range("A1:E5").Select」と記載されます。Rangeを「範囲」と翻訳すれば、Rangeと書く意味が分かると思います。
9行目「ActiveCell.FomulaR1C1 = "3"」とは、要するに現在入力をしようとしているセルに、「3」と入力する。と書かれてあります。ActiveCell「活性化されたセル」、要するに上の行で選択したセルである「A1」を指していますね。「FomulaR1C1」はさっきからA1、A1と言っているのが、R1C1形式で表示した時のセル名を示しているのだよという、ぶっちゃけおせっかいな説明です。R1C1形式って、あの、よく表の上のA列、B列というのが数字になって困ってしまったという話をよく聞きますよね。列名を数字でなくてアルファベットで表示した通常の形式をR1C1形式といいます。
10行目「Range("A2").Select」は、「セルA2を選択する」という意味ですね。これは、マクロを記録した時にEnterキーを押して、結果的にA2を選択していることから表記されています。
11行目「End Sub」というのは、「ここまでがMacro1の操作内容です」という意味です。拝啓に対する敬具のような存在です。

試しに8行目を
Range("A5").Select
と書き換えて保存してください。Excelに戻って「ツール(T)－マクロ(M)－マクロ(M)」でMacro1を選択して「実行(R)」ボタンをクリックしてください。セルA5が3になりましたか？そして、セルA2が選択された状態になっていませんか？

これで解説は終了です。#2の下半分の説明を元に、'---ここから--- という行から '---ここまで--- という行までを貼り付けて実行してみてください。あえて言えば「Sub 担当別シート作成()」の下の「Application.ScreenUpdating = False」から、「End Sub」の上の「Application.ScreenUpdating = True」までをコピーしてやればよいことになります。では今後の学習にお役立てください。

回答No.3

mintan

miechinさん　matsu_jun さん　誠に申し訳ございません。実は私も同じ質問をしたいところでした。でも、VBAの標準モジュールの貼り付け方がわかりません(^_^;)大変お手数ですが、もう少しわかりやすくご教示いただけないでしょうか？　ちなみにEXCELは使えますが、マクロは苦手です。よろしくお願いします。

回答No.1

tubumame

はじめまして。

読解能力がないのか、イマイチやりたいことがよくわかりません。ごちゃ混ぜになっているデータを担当者ごとに別シートにしたいってことですかね？
以下のような感じ。

---------------------------------------

[元データ]

管轄　担当者　店名　販売数
東京　佐藤　 ●●店　100
東京　佐藤　 ××店　120
東京　佐藤　 △●店　 50
大阪　山本　 ▲△店　 83
大阪　山本　 △▲店　 15
大阪　山本　 ●△店　 30
福岡　川田　 ◎×店　 68
福岡　川田　 □×店　 24

---------------------------------------
sheet1

[佐藤さん担当分]

管轄　担当者　店名　販売数
東京　佐藤　 ●●店　100
東京　佐藤　 ××店　120
東京　佐藤　 △●店　 50

---------------------------------------
sheet2

[山本さん担当分]

管轄　担当者　店名　販売数
大阪　山本　 ▲△店　 83
大阪　山本　 △▲店　 15
大阪　山本　 ●△店　 30

---------------------------------------

>担当者の列を削除したいのですが
整理完了後、ごちゃ混ぜデータは１件も
なくなるってことですかね？？

もう少し、情報をいただけるとうれしいです。

お礼

すいません表現がうまくできず・・・
ご回答ありがとうございます。
sheet1、sheet2・・・・のイメージはその通りです。

>担当者の列を削除したいのですが
このことは、追加されるシートの担当者の列を削除したく、ごちゃ混ぜのデータはそのままでも結構です。
ついでに、sheet1、sheet2と追加されるシートのシート名を担当者名に変更したいです。
よろしくお願いします。

補足

補足をお礼のところに記入してしまいました。
sheet1、sheet2・・・・のイメージはその通りです。

>担当者の列を削除したいのですが
このことは、追加されるシートの担当者の列を削除したく、ごちゃ混ぜのデータはそのままでも結構です。
ついでに、sheet1、sheet2と追加されるシートのシート名を担当者名に変更したいです。
よろしくお願いします。