普段VBAを全く使わない初心者で申し訳ありません。
年度初めに作成したいものがあります。

下のシートのようなものがあり、これをグループ「ア」だけで名前と住所が入ったシート、「イ」だけで名前と住所が入ったシートを新規作成していくプログラムを教えていただきたいです。
なお、グループ名は10グループ程になる予定です。

よろしくお願いいたします。

「エクセルのグループ名ごとに別シートを作成」の質問画像

このQ&Aに関連する最新のQ&A

A 回答 (3件)

こんな感じでどうでしょう。

元データのシート名は、Sheet1としています。

Sub sample()
Dim ws As Worksheet
Dim I As Long
'元シートをコピーし作業用シートを作成。グループでソート&重複を削除する。
Sheets("Sheet1").Copy After:=Sheets(1)
Set ws = ActiveSheet
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("C:C"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A:C")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ws.Range("A:C").RemoveDuplicates Columns:=3, Header:=xlYes
'グループ毎のシート生成。
For I = 2 To ws.UsedRange.Rows.Count
If ws.Cells(I, 3) = "" Then Exit For
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
With ActiveSheet
.Range("A:C").AutoFilter Field:=3, Criteria1:="<>" & ws.Cells(I, 3), _
Operator:=xlAnd
.Rows("2:" & .UsedRange.Rows.Count).Delete Shift:=xlUp
.Range("A:C").AutoFilter

.Name = ws.Cells(I, 3)
End With
Next I
'作業用シートの削除。
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End Sub
    • good
    • 0
この回答へのお礼

迅速な回答ありがとうございます。さっそく作ってみます。

お礼日時:2016/03/05 20:56

こんばんは!



すでに回答は出ていますので、参考程度で・・・
標準モジュールです。

Sub Sample1()
Dim i As Long, k As Long, myCol As Long
Dim sN As String, wS As Worksheet, myFlg As Boolean
Application.ScreenUpdating = False
With Worksheets(1)
myCol = .Cells(1, Columns.Count).End(xlToLeft).Column + 2
.Range("C:C").AdvancedFilter Action:=xlFilterCopy, copytorange:=.Cells(1, myCol), unique:=True
.Columns(myCol).Sort key1:=.Cells(1, myCol), order1:=xlAscending, Header:=xlYes
For i = 2 To .Cells(Rows.Count, myCol).End(xlUp).Row
For k = 2 To Worksheets.Count
If Worksheets(k).Name = .Cells(i, myCol) Then
myFlg = True
Exit For
End If
Next k
If myFlg = False Then
Worksheets.Add after:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = .Cells(i, myCol)
End If
sN = .Cells(i, myCol)
Set wS = Worksheets(sN)
myFlg = False
wS.Move after:=Worksheets(i - 1)
wS.Cells.Clear
With .Range("A1").CurrentRegion
.AutoFilter field:=3, Criteria1:=.Cells(i, myCol)
.SpecialCells(xlCellTypeVisible).Copy wS.Range("A1")
End With
Next i
.AutoFilterMode = False
.Columns(myCol).Clear
End With
Application.ScreenUpdating = True
End Sub

こんな感じではどうでしょうか?m(_ _)m
    • good
    • 0
この回答へのお礼

コードを書いて頂き、ありがとうございます。

お礼日時:2016/03/05 21:00

私だったら、グループの数のシートは作成しませんね。


一例ですが、別途 シートに
 A    B   C
名前  住所  グループ
         ア
と云ったシートを準備して

C2セルにグループ名を入力したら
A,B列が表示される仕組みを考えます。
関数でも出来ますが、フィルターオプションの機能が便利です。
http://www4.synapse.ne.jp/yone/excel2010/excel20 …
を参考にしてみてください。
コードは、マクロの記録で簡単に作成できます。
次に、シートモジュールの
Private Sub Worksheet_Change(ByVal Target As Range)
・・・・
End Sub
にコピーすれば、C2セルを変更すると瞬時に希望の一覧になります。
シートは2枚で十分です。
マクロのコードも数行で可能です。
    • good
    • 0
この回答へのお礼

ありがとうございます。フィルターオプションバージョンも作成してみます。

お礼日時:2016/03/05 21:00

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QExcelシートのグループ化時に、一つのシートのみに入力したい(勝手に全シートに入力しないでほしい)

以前検索について質問させていただいたのですが、
http://oshiete1.goo.ne.jp/kotaeru.php3?q=2333302

シートをグループ化するのはいいのですが、グループ化した状態で入力を行うと、(入力したくない)他のシートにも入力されてしまいます。具体的には、Sheet"A"のA1に入力すると、Sheet"B"やSheet"C"のA1にまで変更が反映されてしまいます。今になってそのことに気付いて、データがかなり破壊されてしまい、修復する気も起こりません(TT)

関係ないシートをクリックし、グループ化を解除して入力すれば問題ないのでしょうが、データベース的に使っており検索をしょっちゅうかけるので、不便きわまりません。データを複数シートに分けたのがそもそもの間違いだったのでしょうか?

以上を踏まえ、複数シートに分散したExcelデータベースで検索をひんぱんにかけ、グループ化時にもそのシートにだけ入力したいのです。

何かいい解決案はないでしょうか?

Aベストアンサー

皆さんがおっしゃるようにシートを1枚にする手間を1度だけかけたほうが楽だとは思うのですが・・・。
でも、質問者様の言いたいことも分からないでもないです。
いろいろ考えましたが、マクロを使う以外にいい解決案が浮かびません。

検索して修正ならば・・・検索ではなくCtlr+Hで置換を行えば、該当のシートにのみ変更が反映されます。
(全て置換ではなく、次を検索→該当のものが選ばれた状態で置換と行います)

ただ、なんとなく聞いていると検索は検索、入力は入力で別の作業ということなのかな?
それなら検索だけVlookupなどで作ってあげればいいと思いますが・・・。(全てのシートの最上列に作っておけばどこからでも使えるし)

あとはシート数にもよりますが、マウスを使わずに行えば、少しは作業が楽になるかも知れません。
検索をかけるときはCtrl+Fで検索し、フォームはAlt+F4で閉じます。シートの変更は右手でCtrl+PgUpやPgDn・・・と言うようにやるとか。

全部のシートを選ぶのはたとえば
Sub 全シート選択()
Worksheets.Select
End Sub
こんな記述を書くだけです。
いい機会なのでマクロにも手を出して見てはどうですか?

余談ですが、「グループ化時に、シートの書き込みをすると全シートに書かれてしまう」のは仕様です。
書式設定なんかも全て全シートに適用されるので便利だと思いますが・・・。

皆さんがおっしゃるようにシートを1枚にする手間を1度だけかけたほうが楽だとは思うのですが・・・。
でも、質問者様の言いたいことも分からないでもないです。
いろいろ考えましたが、マクロを使う以外にいい解決案が浮かびません。

検索して修正ならば・・・検索ではなくCtlr+Hで置換を行えば、該当のシートにのみ変更が反映されます。
(全て置換ではなく、次を検索→該当のものが選ばれた状態で置換と行います)

ただ、なんとなく聞いていると検索は検索、入力は入力で別の作業ということなのかな...続きを読む

Qエクセルの住所録で別シートのデータの1列だけ統合

ネットを探してもわからなかったので質問いたします。
エクセルの住所録で別シートのデータ1列だけ統合したいのです。

具体的にはエクセル2010で会社の住所録を作っています。
その住所録で商談会の案内状を出しているのですが、マスター住所録シート(全顧客の住所録)にてお客様のステータスを一列で見たいのです。

私がやっている案内状を出すときの流れは、

例えば2014年神戸での商談会の場合、
1、マスター住所録を別シートにコピーし2014神戸案内状送付とシート名を変更
2、住所順に並べ替え
3、兵庫、大阪、京都のお客様以外を削除
4、個別に追加や削除
5、宛名ソフトにて読み込み住所印刷、発送
~商談会後~
6、マスター住所録に列(2014神戸案内状送付)を追加、新規のお客様を入力し、お客様の行動を入力(来場、購入、新規など)

これが一連の流れです。

やりたいのが案内状を発送したお客様を列(2014年神戸)に「案内」としたいのです。このやり方だとマスター住所録と2014神戸案内状送付の2シートができ、どの人に案内状を送ったのかをマスター住所録にフィードバックできないのです。

案内状を送った人が誰で、誰が来場、購入してくれたのかをマスター住所録にて一覧できたらうれしいです。

エクセル風に書くとこうなります。

シート1(マスター住所録)
A列(お客様番号)B列(お客様名)C列(住所)
1032 高橋正夫京都
1046 渡辺博東京
1123 田中義男兵庫
1162 木村和夫大阪
1256 伊藤政徳北海道
1390 大沢真一兵庫
・・・・・・

↓シートコピー、住所順に並べ替え兵庫、大阪、京都のみ残し、個別に追加や削除、案内状を発送
シート2(2014神戸案内状送付)
A列(お客様番号)B列(お客様名)C列(住所)
1032 高橋正夫京都
1390 大沢真一兵庫
1123 田中義男兵庫
1162 木村和夫大阪
1887 坂口良太岡山
・・・・・・

~商談会後~

↓D列(2014神戸商談会)作成、新規のお客様を追加、
シート1(マスター住所録)
A列(お客様番号)B列(お客様名)C列(住所)D列(2014神戸商談会)
1032 高橋正夫京都来場
1046 渡辺博東京
1123 田中義男兵庫
1162 木村和夫大阪
1256 伊藤政徳北海道
1390 大沢真一兵庫
1887 坂口良太岡山購入
2567 大江雅人京都新規
2568 横井洋介兵庫新規
・・・・・・
これが現在のやり方です。


↑これをこう↓したいのです。

シート1(マスター住所録)
A列(お客様番号)B列(お客様名)C列(住所)D列(2014神戸商談会)
1032 高橋正夫京都来場
1046 渡辺博東京
1123 田中義男兵庫案内(←これを入れたい!)
1162 木村和夫大阪案内(←これを入れたい!)
1256 伊藤政徳北海道
1390 大沢真一兵庫
1887 坂口良太岡山購入
2567 大江雅人京都新規
2568 横井洋介兵庫新規
・・・・・・

2つ質問があります。
Q1、
今後、このようなことを全て手入力でなくできますか?

Q2、
このようなやり方をしているので現在マスター住所録では誰に案内状を送ったのかがまったくわかりません。商談会ごとの案内状送付リストは残っていますがその分も「案内」印をいれられますか?
(ちなみに案内状が返還されてきたりしてマスター住所録から削除したお客様もいままで多くいます)

両方とも一気に解決できる方法があればなお良いですが、Q1、2で方法が別々でもOKです。

質問がわかりにくいとは思いますが、解決できる方、よろしくお願いします。

ネットを探してもわからなかったので質問いたします。
エクセルの住所録で別シートのデータ1列だけ統合したいのです。

具体的にはエクセル2010で会社の住所録を作っています。
その住所録で商談会の案内状を出しているのですが、マスター住所録シート(全顧客の住所録)にてお客様のステータスを一列で見たいのです。

私がやっている案内状を出すときの流れは、

例えば2014年神戸での商談会の場合、
1、マスター住所録を別シートにコピーし2014神戸案内状送付とシート名を変更
2、住所順に並べ替え
3、兵庫...続きを読む

Aベストアンサー

>1、マスター住所録を別シートにコピーし

手順の最初でいきなり別リストを生成してるので、元に戻せなくて困るみたいなお話になります。
エクセルを前提にするなら、データベースの基本的な手順としてはマスターリストに直接「案内」と記入してください。


手順の修正:
マスターリストから対象行(兵庫、大阪、京都)をその場で(オートフィルタの要領で)抽出
マスターリストに直接、案内を記入(必要に応じて追加削除)
案内が記入された行を対象に「宛名印刷」を行う



宛名印刷ソフトは不明なので具体的な手取り足取り説明は割愛しますが、マスターリストを直接元データとして、宛名印刷ソフト側で「案内が記入された行を抜き取って宛名印刷する」ような機能を利用できるかもしれません。例えばワードを使った差し込み印刷なら、そういう操作ができます。

ご利用のソフトにそういう機能が無ければ、案内を記入した後、最後に案内行のみ抽出して別リストに書き出し、宛名印刷を終えたら「そのリストは破棄する」ような段取りでも構わないですね。

Qエクセルのシート名を、シート1に作成した新旧一覧表に対応させる形で、複数シート一括で変更するには?

エクセルのシート名の変更について教えてください。

シート1のA列に、現在のブックのシート名が、
シート1のB列には、変更したいシート名が
対応するように入力されています。
シート数は50~200程度で、つど変わります。


    A列    B列
1行目:りんご   赤色 
2行目:ばなな   黄色
3行目:メロン   緑色
    ・     ・
    ・     ・
    ・     ・

※この場合、「りんご」は「赤色」に、「ばなな」は「黄色」に、
「メロン」は「緑色」に、それぞれシート名を変換したい。

今は、手作業でひとつずつシート名を変換しており、
かなりの手間で困っています。
一覧表を作成するまでは手作業でいいのですが、
何とかして、シート名の変更を、この一覧表を参照して
できないでしょうか?
過去の質問をいろいろと見たのですが、
該当するものが見つけられず、VBAも初心者のため、
途方にくれています。
ぜひともご教授ください。

Aベストアンサー

マクロサンプルです。マクロは標準モジュールシートに貼り付けてください。変換表のシート名は4行目を修正のこと。

Sub Macro1()
Dim sh As Worksheet
Dim res
Const trg As String = "Sheet1" 'シート1のシート名に変更
For Each sh In Worksheets
  Set res = Worksheets(trg).Range("A:A").Find( _
    what:=sh.Name, LookIn:=xlValues, Lookat:=xlWhole)
  If Not res Is Nothing Then
    sh.Name = res.Offset(0, 1).Value
  End If
Next sh
End Sub

Q住所録から特定住所を別シートに抽出分類できるか?

エクセル2010で作成した住所録にある特定の町名をグルーピングして別のシートに分類したいのです。シート1の住所録本体のA列は氏名、B列は町名と丁目(〇〇町◆丁目)、C列は番地を入力します。その他の列にもいくつか入力項目があります。やりたいことは、B列の町名と丁目を入力すると自動的に他のシートにグルーピングして分類できるようにしたいのです。分類の際、次のような場合があります。たとえば、A町1丁目とB町2丁目が同じグループ、つまり同じシートに分類する必要があります。また、単独でC町3丁目は一つのグループとして分類する場合ももちろんあります。マクロ等、エクセルの専門的な知識はもちあわせていません。関数の組み合わせでできそうな気がしていますが、なにぶん技量不足のためわかりません。どなたかご教授ねがいます。宜しくお願いします。

Aベストアンサー

例えば元の表がシート1に有るとしてA3セルから下方に氏名が、B3セルから下方に町名と丁目がC3セルから下方に番地が入力されているとします。F列まで利用されているとしてG列から右の列は作業列します。
G1セルから右の方向にはグルーピングしてそれぞれを異なったシートに表示させるときのシート名を入力します。例えばSheet2、Sheet3、・・・・ などと入力します。
G2セルから右横方向のセルにはA町1丁目などと入力します。同じグループに二つの町名が入る場合にはA町1丁目/B町2丁目などと/を入れて入力します。ここでは2つの町名までを1グループとして処理できる方法を示します。
そのためにG3セルには次の式を入力して右横方向にドラッグコピーしたのちに下方向にもドラッグコピーします。

=IF(COUNTIF(G$2,"*/*")>0,IF(COUNTIF($B3,"*"&LEFT(G$2,FIND("/",G$2)-1)&"*")+COUNTIF($B3,"*"&MID(G$2,FIND("/",G$2)+1,10)&"*")>0,MAX(G$2:G2)+1,""),IF(COUNTIF($B3,G$2)>0,MAX(G$2:G2)+1,""))

これで同じグループに所属する場合には1から順に番号が表示されます。
そこでお求めの表ですがグループごとに表示させるシートについて(シート1を除く)Ctrlキーを押しながらそれぞれの名前のシート見出しをクリックして同じ作業グループを作ります。
そのうちのあるシートが画面にあるわけですがそのシートのA1セルにはそのシートのシート名を例えばSheet2のように入力します。
2行目にはシート1と同じ項目名を入力します。
A3セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(A$2="","",IFERROR(INDEX(Sheet1!$A:$F,MATCH(ROW(A1),INDEX(Sheet1!$A:$XX,1,MATCH($A$1,Sheet1!$1:$1,0)):INDEX(Sheet1!$A:$XX,1000,MATCH($A$1,Sheet1!$1:$1,0)),0),COLUMN(A1)),""))

2行目には必ず項目名を入力ことが必要です。
最後にシート1を選択することで作業グループを解除します。
シート1を除く他のシートについてA1セルにシート1で決めたグループごとのシート名を入力することで、そのグループだけのデータが表示されることになります。

例えば元の表がシート1に有るとしてA3セルから下方に氏名が、B3セルから下方に町名と丁目がC3セルから下方に番地が入力されているとします。F列まで利用されているとしてG列から右の列は作業列します。
G1セルから右の方向にはグルーピングしてそれぞれを異なったシートに表示させるときのシート名を入力します。例えばSheet2、Sheet3、・・・・ などと入力します。
G2セルから右横方向のセルにはA町1丁目などと入力します。同じグループに二つの町名が入る場合にはA町1丁目/B町2丁目などと/を入れて入...続きを読む

Q【Excel】複数あるシート上の住所録を重複なく別のシートへまとめる方法を調べています

はじめまして、こんにちは。
仕事でエクセルを使って困ってしまいました。

現在、社内の各部署が抱えている顧客住所録を、エクセルでシートごとにまとめているのですが(住所の入力フォーマットは共通です)、
これらをさらに別のシートへ重複なくまとめた「一覧表」を作成したいと思っています。

普段はコピー&ペーストでしのげるのですが、各部署の住所録は各人が随時アップデートされるため、その都度コピペをしていると手間となってしまいます。

そこで部署の住所録シートに入力があれば、自動的に「一覧表」シートへの入力も済まされている状態にしたいと考えているのですが、現在まで試行錯誤している次第です。。

そこで下記の要件を満たすにはどのようにすればよいかご指南をお願いいたします。

 ・各シートにある住所録を、さらに新しい別のシート「一覧表」へまとめたい
 ・「一覧表」へまとめられた住所録では、重複を省きたい。
 ・各シートへ新規に入力した住所は、自動的に「一覧表」へも入力されるようにしたい。

Aベストアンサー

#01です。
「一覧表」シート3行目以降に。他のシートの3行目以降のデータをコピーして集約するマクロです。「一覧表」というシートを作成してから動かして下さい。

マクロは「ALT+F11」でVBE画面を表示し、左上のVBAプロジェクトエクスプローラの「該当シート名右クリック」→「挿入」→「標準モジュール」を選択して開いた画面に貼り付けて下さい。

実行はシート画面に戻り、「ALT+F8」でマクロ一覧を開き、「マクロ名を選択」→「実行」ボタンです。マクロの実行結果はUNDOで戻せないので、予めSAVEしておいて下さい。

Sub Macro()
Dim ws As Worksheet
Dim LastR, LastR2 As Long
 Application.ScreenUpdating = False
 With Worksheets("一覧表")
  .Activate
  LastR = .Range("A65536").End(xlUp).Row
  If LastR > 2 Then
   .Range("A3:G" & LastR).ClearContents
  End If
  .Range("A3").Select
  For Each ws In Worksheets
   If ws.Name <> "一覧表" Then
    LastR2 = ws.Range("A65536").End(xlUp).Row
    ws.Range("A3:G" & LastR2).Copy
    ActiveSheet.Paste
    .Range("A65536").End(xlUp).Offset(1, 0).Select
   End If
  Next ws
 End With
 Application.CutCopyMode = False
 Application.ScreenUpdating = True
End Sub

もしブックを開いたときに自動で実行されることを希望されるなら、先頭の一行を以下に置換した上で
 Private Sub Workbook_Open()
VBE画面で「ThisWorkBook右クリック」→「コードの表示」で開く画面に貼り付けると、ブックが開くたびに実行されます。

#01です。
「一覧表」シート3行目以降に。他のシートの3行目以降のデータをコピーして集約するマクロです。「一覧表」というシートを作成してから動かして下さい。

マクロは「ALT+F11」でVBE画面を表示し、左上のVBAプロジェクトエクスプローラの「該当シート名右クリック」→「挿入」→「標準モジュール」を選択して開いた画面に貼り付けて下さい。

実行はシート画面に戻り、「ALT+F8」でマクロ一覧を開き、「マクロ名を選択」→「実行」ボタンです。マクロの実行結果はUNDOで戻せないので、予めSAVE...続きを読む


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング

おすすめ情報