[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『マクロ：全シートの同セルの同じ値の数字を転記して一覧を作成』（marco3）
ExcelのVBAを使用して、下記のようなものを作りたいと思ってます。
・シートは複数（『一覧』シート以外の全シート）を対象
・各シートの同セルの値（S1）を順番に取得し、『一覧』シートのA2から下に（A2、A3、A4…）取得した値を転記していく。
（A1は一覧表の項目名の記載有）
（このマクロを実行するにあたって、常に『一覧』シートの1行目以外は全部データは削除する仕様になっていると仮定して）
例）
シート名：Sheet1、Sheet2、Sheet3、一覧
セルS1の各シートの値：一郎、次郎、三郎
↓
結果
「一覧」シート
A2：一郎
A3：次郎
A4：三郎
どのようなやり方が一番効率的なのか、初心者なので、ヒントをいただけると嬉しいです。
セルの値はS1以外にもありますが、やり方がわかれば、記述を増やすだけだと思ってます。
あと、その他 気を付けるべき点があれば教えていただきたいです。
よろしくお願いします。
＜ 使用 Excel：Excel2013、使用 OS：Windows8 ＞
Option Explicit
Sub main()
Dim sh01 As Worksheet
Dim i As Long, j As Long
Set sh01 = Worksheets("一覧")
j = 2
sh01.Range("A:A").ClearContents
For i = 1 To ThisWorkbook.Worksheets.Count
If Sheets(i).Name <> "一覧" Then
With Sheets(i)
sh01.Cells(j, 1) = .Cells(1, 19)
j = j + 1
End With
End If
Next
End Sub
（隠居じーさん） 2018/05/14(月) 14:42
＞どのようなやり方が一番効率的なのか すみません。（隠居じーさん） 2018/05/14(月) 14:42は 効率的かどうかは分かりません、ただ動くだけなので。。。 (;^_^A 他の方のご回答をお待ち下さい。 シート指定には気を使ったつもりですが。。。 外していましたら、すみません。 でわ （隠居じーさん） 2018/05/14(月) 14:58
ありがとうございました。
こうやってコードを見るとなるほど！と思うのですが、まだまだ全然勉強不足ですね。
ところで、私の質問の仕方が悪かったのですが、もしシート名が「sheet1」といった具合ではなく、色々なシート名があり、「一覧」シートに転記する場合はどうなるのでしょうか？
例）
シート名：A工事、あいう工事、カタカナ工事、一覧
もし、回答いただけるのであれば、よろしくお願いします。
（marco3） 2018/05/14(月) 15:09
そして、横道に逸らしてしまう事をご容赦ください。 ご質問への直接回答ではないのですが、隠居じーさんさんのコードを見て気になった点なぞ。
1つは、ThisWorkbookを明示している箇所と省略している箇所が混在しているので、どちらかに統一すべきという点。 ThisWorkbookはデフォルトなので、省略で統一した方が、コードの文字数が少なくなって、見せたい箇所が明らかになると思うのですよ。
もうひとつは少しだけ重要ですが、WorkSheetsオブジェクトとSheetsオブジェクトは別物なのに、混在して使っている点。RangeとCellsのように、同じものを指していると理解していませんか？ 調べて頂くと判ると思いますが、対象が少し違うので、番号が違う可能性があるのです。 PicturesとShapesの関係みたいなもんです。 なので、どちらかに統一すべきなのですが、現在ではほぼ同じシートを表現するので、文字数が少ないSheetsで統一するのをお薦めしておきます。
（???） 2018/05/14(月) 15:17
こんにちは ＾＾ ？？？さんが既に回答されていますが。 たくさん、シートこさえて、試して見てください。
？？？さん 、フォローありがとう御座います。 WorkSheetsオブジェクトとSheetsオブジェクト 何となく使っていましたが。とても勉強になります。 （隠居じーさん） 2018/05/14(月) 15:38
ありがとうございます。
ようやく、実装して確認したところ、シート名関係なく動くことが分かりました。
私の意味不明な質問でお手間お掛けしてしまって申し訳ないです。
＞？？？様
ありがとうございます。
ちゃんと動作確認してから、質問は今後させていただきますね。
WorkSheetsオブジェクトとSheetsオブジェクト…
実はRangeとCellsの違いもきちんと理解していないので、先ほど自分で調べてみました。
奥が深くて（深すぎて）なかなか一歩二歩進めないのですが、このように皆さんが教えていただけること感謝しています。
最後に、実は今回シート数が300位になったExcelのデータを集計したくて2年間やれずじまいで、調べたけど、色々書籍読んだけど、前に進めませんでした。
たった一つの質問で先ほど、膨大なデータを一覧にすることができました。
本当にありがとうございました<(_ _)>
（marco3） 2018/05/14(月) 16:22
＞どのようなやり方が一番効率的なのか、
状況によってケースバイケースなのでなんともいえないように思います。
Sub Sample1()
Dim i As Long
For i = 1 To Worksheets.Count - 1
Worksheets("一覧").Range("A" & 1 + i).Value = Worksheets(i).Range("S1").Value
Next i
End Sub
Sub Sample2()
Dim i As Long, sh As Worksheet
i = 1
For Each sh In Worksheets
If sh.Name <> "一覧" Then
Worksheets("一覧").Cells(1 + i, "A").Value = sh.Range("S1").Value
i = i + 1
End If
Next sh
End Sub
Sub Sample3()
Dim i As Long, MyRNG As Range
Set MyRNG = Worksheets("一覧").Range("A2")
For i = 1 To Worksheets.Count
If Worksheets(i).Name <> "一覧" Then
Worksheets(i).Range("S1").Copy MyRNG
Set MyRNG = MyRNG.Offset(1, 0)
End If
Next i
End Sub
たとえば、一覧シートが毎回ブック最後（一番右側）にあるっていうのが前提になるのであれば、一覧シートだったら処理しないなんてことは考えずに上記のSample1のようにすればいいと思います。（必ず１番目（一番左）にあるという場合も同じ考え方ができますよね）
逆に、一覧シートの位置は不定ですというのであれば、隠居じーさんさんが示されておられるように、シート名などで処理対象か判定する必要がでてくるように思います。
＞気を付けるべき点があれば
気をつけるというか、今回の処理であれば、似たような処理を繰り返していることはおわかりになりますでしょうか。
わかるのであれば、自ずと何らかの条件に基づいてループ処理をさせればよいことに気づきますよね。
そうなるとポイントとしては
(1)どうやってループを回すのか
「Do 〜 Loop」「For 〜 Next」、「For Each 〜 Next」など
(2)コピー元から貼付先にどうやって値を持って行くか
「値の参照：〜．Value = 〜．Value」、「COPYメソッドの実行：〜.Copy 〜」など
なんてところに注目して、自分が使いやすいのを選択していくのがいいんじゃないかなとおもいます。
ちなみに、見れば解ると思いますが、
Sample1、Sample3は「For 〜 Nextステートメント」、Sample2は「For Each 〜 Nextステートメント」でループさせています。
また、
Sample1、Sample2は、「（貼付先の）Valueプロパティに（コピー元の）Valueプロパティを参照し値を設定」していますが、Sample3では「（引数に貼付先を指定した）Copyメソッドを実行」し、コピペしています。
（もこな２） 2018/05/14(月) 18:21
こんばんは ＾＾ 解決されたみたいで、よかったですね
修正版です。グラフ、ダイアログ、シート等、がもしあればセル参照がエラーになるかも？
なので。ワークシートに統一しました。
???さん 有難うございました。
Option Explicit
Sub main()
Dim sh01 As Worksheet
Dim i As Long, j As Long
Set sh01 = Worksheets("一覧")
j = 2
sh01.Range("A:A").ClearContents
For i = 1 To Worksheets.Count
If Worksheets(i).Name <> "一覧" Then
With Worksheets(i)
sh01.Cells(j, 1) = .Cells(1, 19)
j = j + 1
End With
End If
Next
End Sub
（隠居じーさん） 2018/05/14(月) 21:55
効率的＝同じことを2度書くなら変数を使う
という定義で考えると、
こんな感じですかね。。。。
Sub test()
Dim wsResult As Worksheet
Dim ws As Worksheet
Dim ix As Long
ix = 1
Set wsResult = Worksheets("一覧")
For Each ws In Worksheets
If Not ws Is wsResult Then
ix = ix + 1
wsResult.Cells(ix, "A").Value = ws.Range("S1").Value
End If
Next
End Sub
どんな基準で効率的かを評価するかわかりませんが、
他の方のサンプルをみて、
「一覧」という文字列が何度も出てくるなら定数で宣言したいなと思いました。
（まっつわん） 2018/05/15(火) 13:00
＞隠居じーさん様
再度ありがとうございました。
正直、色々な方のコメント頂き、本当に参考になりました。
ちょっと出だしでパニクっての質問で横着な感じでしたが、結果色々とコメントもらったおかげで勉強になりました☆
＞まっつわん様
今自分でできる方法で…
コメントありがとうございます。
実は超初心者でどのコードを使っていいのかも分かりませんでした。
やりたいことは分かってるけど、言葉にもできるけど（ある程度）、それをコードで表現することができず苦しんでます。
とはいえ、自分なりに考えて作って、そこから簡易的に落とし込んでいくことも今後大事ですね。
色々な方の貴重な意見をいただき、小さな会社で一人事務をやっている身分として、いつもこちらの皆さんのコメントや投稿を参考にさせてもらって、本当に助かってます。
変数もついつい、初めに色々と考えすぎて、先に進んでないのが現状です。
何回も書いてるなら変数…
↑まさにこれこそ、「とりあえず、やってみてから後から考えてみる」ですね。
ありがとうございました！
（marco3） 2018/05/15(火) 13:44
>実は超初心者でどのコードを使っていいのかも分かりませんでした。 初心者でもそうでなくても知らなければ聞けばいいのですが、 なかなか、どう聞いていいかわからないですよね。
まず、
エクセルのファイルは、
エクセルから見ると、ブックと呼びます。
で、そのブックはいくつかのシートで構成されています。
このたくさんのシートの集まり（一つでも集まり）を、
「Worksheets」
と、表現します。
そしてその中の一つのシート、例えば「一覧」というシートは、
Worksheets("一覧")
と表現します。
やりたいことは、
ブック内のシート全部を巡回して作業をしたいですよね？
こういう、集まりの中を巡回するときは、
For Each 〜 ステートメントを使用して、巡回します。
For Each ws In Worksheets
ワークシート群の中の各要素を変数wsに順番に入れていきます。というような意味になるでしょうか？
文法的に日本語にしずらいですね＾＾；
シート上のセル範囲もセルの集まりですので、
集まりに対してはFor Each を使うといいですが、
やりたいことによっては、使えない場合が出てくるかもしれませんので、
その辺は臨機応変に対応しましょう。
（まっつわん） 2018/05/15(火) 14:36
ありがとうございます。
丁寧で分かりやすいコメントで「そうそう！」と。
ExcelVBAの参考書なるものはたくさん買ってみたけど、こういった時にこうやりたい（こういうコードを使う）的なものが一番実用性があると思いつつ、パニックになっているのでしょうね。
皆さんの提供して頂いたコードを見ると分かるけど、それを自分が組み立てていくとなった時の部品を実は使いこなせてない（理解していない）ので、応用・流用できてないだと思います。
あまり深く考えず、完璧を求めず、面倒がらずに今後取り組んでいこうかと思いました。
色々とありがとうございました<(_ _)>
（marco3） 2018/05/15(火) 14:49
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.