質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.21%

フォルダー内の複数ブックから、同じシート名の同じセルを別ブックのシートに一覧にしたいです。

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 2,476

fuchi

score 13

 フォルダー内の複数ブックから、同じシート名の同じセルを別ブックのシートに一覧にしたいです。

ネットで調べて作成してみました。フォルダ内にあるブックから転記されるものの、
希望するシートから、転記されるものとされないものがあり困っております。

【質問】
①別ブックで転記したいセルの指定をするSheetが、作成される転記一覧と同じシートです。 転記一覧のSheetと分けることは可能でしょうか。
(↑無理な気がしますので質問を取り消します。)

②フォルダ内のブックのすべてのシートから、指定したセルの転記がされます。 「集計」シートのみの、セルを転記することはできるでしょうか。

➂マクロ実行した際に出る、「勤務表(社員名).xlsxの変更を保存しますか」を出ないようにする方法はありますか?
(↑PCをWindows7変えたところ、保存しますかが表示されなくなり、原因はわかりませんが一覧表の作成ができました。)

PC環境
Windows7、
Windows10 Excel2010
・共有フォルダー内に、20個の勤務表のブックがあります。
個人で勤務時間を、日々更新していきます。

【個人のブック・1年分の勤務表】シートのタブはこんな感じで、13シートあります。↓ 

集計 4月 5月 6月 7月 8月 9月 10月 11月 12月 1月 2月 3月

4月から3月の12シートの内容は勤務表です。不要かと思いますので詳細は省きました。
~~~~~~~

【個人のブックの13シート目・「集計」シート】↓

「予定累計時間」と「実績累計時間」の2つのセルを、 12か月分参照しています。

A B C D E F G H I
1 4月 4月 5月 5月 6月 6月 7月 7月...
2 予定累計 実績累計 予定累計 実績累計 予定累計 実績累計 予定累計 実績累計...
3 中田 160:00 180:00 200:00 160:00 180:00 175:00 175:00 190:00...

今までこの一つ一つのファイルを開き、「集計」シートでの累計確認をしていました。
人数が多くなってきたので、別ブックのファイルに転記をして、
一つのファイルを開くだけで、全員の合計累計時間を確認できるようにしたいと思っています。


【別ブック】今回作成したVBAのファイル。(マクロ実行前)
「Sheet1」
1行目は項目名のようなものなので固定です。
2行目は、項目名にあたる参照させたいセルを、手入力でセルを指定するようになっています。
(個人のブックの13枚目、B3~Z3までを指定しました)

できれば、マクロ実行後、行や列の削除をせず表をそのまま使用したいです。
この状態だと、A列ファイル名と2行目のセル指定の入力行を削除することになるからです。

別シートにセル指定のための、入力するシートを作りたかったですが
やり方がわからずSheet1の上部に入力するようになっています。

A B C D E F G H I
1 ファイル名 名前 4月予定 4月実績 5月予定 5月実績 6月予定 6月実績 7月予定
2 A3 B3 C3 D3 E3 F3 G3 H3
3 実行すると取得したファイル名が入る
4

【Sheet1】


【別ブック】今回作成したVBAのファイル。(マクロ実行後)

・フォルダ選択画面面が出てきます。
・フォルダ参照元(共有フォルダー)を選択します。

この時点で、参照元の個人ブックが開き、
「勤務表(社員名).xlsxの変更を保存しますか?」全員分聞いてきます。
「保存しない」を選択すると、無事転記されます。(別シートが転記されるものもあります。)
何度も押さないといけません。これを出ないようにできるでしょうか。

【別ブック】1年分、全員の月累計(予定と実績)を一覧表にしていきたい。

A B C D E F G H I
1 4月 4月 5月 5月 6月 6月 7月 7月
2 予定累計 実績累計 予定累計 実績累計 予定累計 実績累計 予定累計 実績累計
3 中田 160:00 180:00 200:00 160:00 180:00 175:00 175:00 190:00
4 安藤 165:00 0:00 18:00 175:00 175:00 180:00 175:00 180:00
5 吉田 250:00 125:00 256:00 165:00 170:00 300:00 160:00 180:00
6 鈴木 165:00 170:00 300:00 160:00 180:00 200:00 160:00 180:00

【Sheet1】


 発生している問題・エラーメッセージ

エラーメッセージ
「勤務表(社員名).xlsxの変更を保存しますか?」(Win10でのみ)

### 該当のソースコード

 Option Explicit
Sub MakeDataList()
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Dim srcWS As Worksheet
Set srcWS = ActiveSheet
Dim folderPath
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show <> True Then Exit Sub
folderPath = .SelectedItems(1)
End With
Dim row As Long
Dim col As Long
Dim lastCol As Long
row = 3
lastCol = srcWS.Range("B2").End(xlToRight).Column
If lastCol = Columns.Count Then
MsgBox "取得位置が範囲が未定義です "
Exit Sub
End If
Dim file
For Each file In fso.GetFolder(folderPath).Files
With Workbooks.Open(folderPath & "\" & file.Name)
srcWS.Cells(row, "A").Value = file.Name
For col = 2 To lastCol
srcWS.Cells(row, col).Value = .Worksheets(1).Range(srcWS.Cells(2, col).Value).Value
Next
.Close
row = row + 1
End With
Next
End Sub

```

 試したこと

異なるPCでのマクロの実行

 補足情報(FW/ツールのバージョンなど)

ここにより詳細な情報を記載してください。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • wwbQzhMkhhgEmhU

    2018/10/08 09:32

    ちゃんと読んでないし、当てずっぽうですが、自身も開こうとして、保存するか聞かれてるとか...

    キャンセル

  • fuchi

    2018/10/08 09:44

    とても長い文章になってしまいましてすみません。 私自身が転記元のファイルを開く操作をしているのではないかということでしょうか。もしそうであれば、そういったことはしておらず、フォルダも閉じている状態です。なぜかファイルが勝手に開き表示されてしまう状態です。

    キャンセル

  • wwbQzhMkhhgEmhU

    2018/10/08 16:30

    転記先のファイルが転記元のファイルと同じフォルダにあって、二重に開こうとしていないか聞いてみました

    キャンセル

  • fuchi

    2018/10/08 18:17

    コメントくださりありがとうございました。試しに転記先と転記元ファイルを同じフォルダに入れて実行してみました。「実行時エラー1004Excelファイルで○○.xlsmを開く事ができません。ファイル形式またはファイル拡張子が正しくありません。」と表示され同じフォルダでは使用できないようでした。

    キャンセル

回答 2

checkベストアンサー

+2

②フォルダ内のブックのすべてのシートから、指定したセルの転記がされます。 「集計」シートのみの、セルを転記することはできるでしょうか。

srcWS.Cells(row, col).Value = .Worksheets("集計").Range(srcWS.Cells(2, col).Value).Value


といった感じでシート名を指定してあげればいいかと思います。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/10/08 23:08

    どうもありがとうございます!
    無事希望していた事ができました。本当にたすかりました!

    キャンセル

+1

➂のみ回答します。保存せずに終了を指定できます。

.Close Savechanges:=False

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/10/08 18:34 編集

    ありがとうございます。回答いただいた直後に場所を移動することになり、
    移動先の別のPC(同じWin7)で、再度マクロを実行したところ③でご相談していた「○○.xlsxの変更を保存しますか」が表示されずスムーズに一覧の作成が出来ました。
    ですので、いただいたマクロはまだ追加していませんが、いつまた出てくるかわかりませんのでそのときに使わせて頂きます。ありがとうございます!

    【訂正】
    間違えました。同じOSではありませんでした。
    最初はWindows10でうまくいかず、Windows7ではメッセージが出ず成功しました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.21%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる
  • トップ
  • VBAに関する質問
  • フォルダー内の複数ブックから、同じシート名の同じセルを別ブックのシートに一覧にしたいです。