元データ(ピボッド)を開かずマクロで更新したい。

同一フォルダ内の複数ブックの中に同じフォーマットの複数シートがあります。
その複数シートにSUMIFS関数で集計用の数式を組んでおり、その参照先元データ(ピボッド)が別ブックにあります。

以前、SUMIFS関数が別ブック参照エラーになる関数だと知り、数式を他の関数で組むか元データのピボッドをシートごとコピーするか？と教えていただいたので、元データシートを複数ブックにそれぞれコピーする方法を選択しました。
その各ブックにコピーした元データシートを元データを更新すると開かずともすべて更新できるようなマクロを組みたいです。

もしくは、他に何か良い方法があればご教授頂ければ幸いです。
何卒、よろしくお願いいたします。

質問者からの補足コメント

• >３）参照先元データ(ピボッド)のブックのシートのフォーマット（セルの位置情報）
  >（SUMIFS関数で集計用の数式に関するセルの位置情報は必須です。）
  1部→A列：売上日付　B列：商品コードの一桁目　C列：担当者名　D列：支店名　E列：課or営業所名　F列：部門名　G列：商品1or2の条件作業列
  　H列：1or0の条件作業列　I列：新規(1)or既存の条件作業列　J列：数量　ｋ列：売上
  2部→A列：売上日付　B列：商品コードの一桁目　C列：担当者名　D列：支店名　E列：課or営業所名　F列：部門名　G列：新規(1)or既存の条件作業列
  　J列：数量　ｋ列：売上
  3部→A列：売上日付　B列：商品コードの一桁目　C列：担当者名　D列：支店名　E列：課or営業所名　F列：部門名　G列：1.2.3.4.5.6.7.8の条件作業列
  　H列：新規(1)or既存の条件作業列　I列：数量　J列：売上

  No.4の回答に寄せられた補足コメントです。 補足日時：2017/05/25 11:38

• >４）複数ブックの中に同じフォーマットの複数シートのブック名、シート名
  →計画のフォルダに下記ブックが入っています。
  「関東支店1課、2課、3課、西日本支店1課、2課、3課、中部支店1課、2課、3課、東北支店1課、2課、3課、九州支店、中国支店、本部、横浜営業所、栃木営業所」の17ブックあります。
  シートはそれぞれ担当者名になっており、支店によってシート数が異なります。
  それに加え、2）で記載した④各部の前年P、⑤各部本年Pのシートコピー分と下記①②のシートがあります。
  ①別途、質問投稿しご回答いただいた合計シート：「合計シート」
  ②それとは別の条件での合計シート：「それ以外シート」

    補足日時：2017/05/25 11:40

• >５）上記シートのセル情報、及び、そのセルのどこにSUMIFS関数の結果を格納したいのかの情報
  →3）で記載したピボッドの作業列を参照してそれぞれ、前年ピボッドは前年のセルへ、実績は実績のセルへ結果を表示させたいです。ちなみに、A7の「1」は新規だったら「1」とする作業列です。
  数式を表示しましたが、説明が足りないようでしたら申し訳ありません。
  >６）SUMIFS関数で集計している機能を業務的に説明したもの
  →月別で個人の特定商品の前年、本年の実績を年間で管理したい。それぞれ条件が多いので作業列を部別で作成しています。質問に対しての回答が違っておりましたら、申し訳ありません。

    補足日時：2017/05/25 11:41

• ※個人別のシートの項目欄を添付します。
  2つに並べてありますが、実際は縦になっています。

  
    補足日時：2017/05/25 11:42

• 個人別のシートに組んでいる数式の画像です。
  ピボッドのデータとA列の作業列を参照する式になっています。
  ※画像が見づらい等ありましたら、教えてください。

  
    補足日時：2017/05/25 11:49

• >４）ピボットのコピーを行っているとのことですが、これは、関数で参照するときに、エラーを発生させない為と理解しました。
  →おっしゃる通りです。
  >マクロであれば、元データのピボットの参照が可能なので、ピボットのコピーは不要と考えますがいかがでしょうか。
  > (ピボットをコピーするなという意味ではありません。マクロは、コピーされたピボットを参照しないで、コピー元のピボットを参照するという意味です）
  →はい、コピー不要で元データピボッド参照ということが可能であればそちらで処理ができたら幸いです。

  No.5の回答に寄せられた補足コメントです。 補足日時：2017/05/26 15:10

• ※個人シート3部の画像添付します。
  ★3部集計項目詳細
  3部→A列：売上日付、B列(集計条件不要)、C列：担当者名、D列：支店名、E列：所属名、F列(集計条件不要列)、G列：作業列①(1～8のどれかだったら)、H列：作業列②(新規だったら1)、I列：数量、J列：売上
  ・項目①=G列の中で「1」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量
  ・項目②=G列の中で「2」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量
  ・大分類A(既存)=G列の中で「3」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量

  
    補足日時：2017/05/29 14:33

• ※個人シートの1.2部の画像添付します。
  ・大分類A(新規)=G列の中で「3」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だったら、H列の中で1だった時の数量
  ・大分類B(既存)=G列の中で「4」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量
  ・大分類B(新規)=G列の中で「4」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だったら、H列の中で1だった時の数量
  ・大分類C(既存)=G列の中で「5」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量

  
    補足日時：2017/05/29 14:35

• 

  ※文字数の関係で補足が分けての投稿になってしまい、わかりづらくて申し訳ありません。
  ・大分類C(新規)=G列の中で「5」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だったら、H列の中で1だった時の数量
  ・大分類D=G列の中で「6」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量
  ・大分類E=G列の中で「7」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量
  ・大分類F=G列の中で「8」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量

    補足日時：2017/05/29 14:36

• 3）の画像ですが、3部まとめて項目並び順を添付します。

  
  No.10の回答に寄せられた補足コメントです。 補足日時：2017/05/31 11:18

No.37 ベストアンサー 回答者： tatsu99 回答日時： 2017/06/05 23:01

エラーメッセージは、

①○○○○○が存在しません
②○○○○○内に支店別＆課別（個人データ）ブックが存在しません。
のうち、①のほうで間違いないでしょうか。○○○○○は、実績ファイル名です。

①の場合、以下の確認作業を行ってください。（②なら計画フォルダ内に支店別＆課別のブックがないエラーなので論外です）

80行以降を以下のように変更する
'売上明細ブック名設定
Uri_path = Jis_folder & "\" & Uri_book
MsgBox ("ファイル名<" & Uri_book & ">") '追加①
MsgBox ("フルパス名<" & Uri_path & ">") '追加②
MsgBox (GetAttr(Uri_path)) '追加③
If Dir(Uri_path) = "" Then
MsgBox (Uri_path & "が存在しません")
Exit Sub
End If

追加①②は、昨日追加分
追加③が今回追加分です。

ケースA：追加③でエラーが発生した場合、物理的にそのファイルが存在しません。
ケースB：追加③でエラーが発生しない場合は、数字が表示されます。32が正常値です。
34又は35の場合、隠しファイルになっている可能性があります。
32又は34の場合、「○○○○○が存在しません」のエラーメッセージが表示されます。

ケースA,Bのどちらになるか、確認をお願いします。

No.42 回答者： tatsu99 回答日時： 2017/06/07 11:34

>ちなみに、最近隠しフォルダの設定をした記憶がありそれが原因になっているのかもしれないと不安になったのですが。

。
>ただ、表面上はちゃんと表示されており、隠しファイルのチェックも外れているのですが他に何か原因となることがあったりするのでしょうか？

隠しファイルであれば、実行時エラーは発生しません。（○○が存在しませんと表示されます）
隠しフォルダでの試験は、こちらでは、やっていませんので、正確なコメントはできませんが、
新たにCまたはDドライブに試験用のフォルダを作成できませんでしょうか。
その際は、全角、空白などの混じらない、簡単な、TESTDIR　などが良いと考えます。

この回答へのお礼

>隠しファイルであれば、実行時エラーは発生しません。（○○が存在しませんと表示されます）
>隠しフォルダでの試験は、こちらでは、やっていませんので、正確なコメントはできませんが、
>新たにCまたはDドライブに試験用のフォルダを作成できませんでしょうか。
>その際は、全角、空白などの混じらない、簡単な、TESTDIR　などが良いと考えます。
→隠しフォルダの件、承知いたしました。Cドライブに「TESTDIR」でフォルダ作成しました。

お礼日時：2017/06/07 11:39

No.41 回答者： tatsu99 回答日時： 2017/06/07 10:56

>→申し訳ありません。

補足を上限まで使用しており、こちらに画像添付することができません。
>他に何か方法はありますでしょうか？
では、一旦、この質問を閉じて、新たな質問として、続きを行いましょうか。
質問のこのURLを提示し、その続きの旨を記述すれば、よいかと。

この回答へのお礼

ご提案ありがとうございます。
後程、新たな質問として投稿いたします。
その際、画像も添付いたしますがやはりファイルがないと表示されてしまいます。。。
ちなみに、最近隠しフォルダの設定をした記憶がありそれが原因になっているのかもしれないと不安になったのですが。。
ただ、表面上はちゃんと表示されており、隠しファイルのチェックも外れているのですが他に何か原因となることがあったりするのでしょうか？

お礼日時：2017/06/07 11:26

No.40 回答者： tatsu99 回答日時： 2017/06/07 10:42

No39の提示例では、

売上明細データ(201705).xlsx
のような名称の例を提示していましたが、
（）があるため、全角、半角での間違いがこのファイルの場合、発生しやすいです。
AAA.xlsx とか　BBB.xlsmとかの簡単な名称で試験し、そのエビデンスの提示をお願いします。

No.39 回答者： tatsu99 回答日時： 2017/06/07 10:07

>№37で実行しましたところ、こちらで記載頂いたエラーがでました。

。。
では、本当に、指定されたファイルがないということになります。
拡張子も含めて、合ってますか。
管理シートでAAA.xlsm を指定し実際のファイルはAAA.xlsxだったなどということはないでしょうか。

もし、間違いなければ、
あなたが指定したファイルが間違いなく存在するというエビデンス（証拠）の提示をお願いします。
添付図の①のように、管理シートで、指定したD2,E2の内容の画像
添付図の②のように、上記のファイルが存在するディレクトリに移動し、
コマンドプロンプトで、dir を実行し、指定したファイル名が表示されることの確認。
（特に、赤字で囲んだ箇所が、間違いないか確認し、その箇所を含んだ画像を提示しください）
鮮明な画像の提示をお願いします。
第三者がみれば、間違いに気づく可能性があります。
操作で不明な点があれば、補足ください。

この回答へのお礼

早速、ご返信頂きましてありがとうございます。

>では、本当に、指定されたファイルがないということになります。
> 拡張子も含めて、合ってますか。
> 管理シートでAAA.xlsm を指定し実際のファイルはAAA.xlsxだったなどということはないでしょうか。
→こちらに関してはどちらも.xlsxで間違いありません。

>もし、間違いなければ、
>あなたが指定したファイルが間違いなく存在するというエビデンス（証拠）の提示をお願いします。
→申し訳ありません。補足を上限まで使用しており、こちらに画像添付することができません。
他に何か方法はありますでしょうか？

何度も本当に申し訳ありませんが、何卒ご教授願います。

お礼日時：2017/06/07 10:51

No.38 回答者： tatsu99 回答日時： 2017/06/05 23:38

No37の補足です。

ケースAの場合、添付図のようになります。
①が表示され、デバックをクリックすると②が表示されます。

この回答へのお礼

ご返信が遅くなり申し訳ありません。
№37で実行しましたところ、こちらで記載頂いたエラーがでました。。。

お礼日時：2017/06/07 09:40

No.36 回答者： tatsu99 回答日時： 2017/06/05 15:30

すみません。

これからでかけますので、戻りは夕方になります。
基本的なことですが、管理シートは、このマクロのあるブックの"管理"シートです。
このマクロ実行時、他のブックを開かないでください。
他のブックを開いた状態で、そのブックに"管理"のシートがあり、そのブックがアクティブ状態に
なっていると、そちらを参照する可能性があります。

この回答へのお礼

お忙しいのに何度もお手数をおかけして申し訳ありません。
明日で結構ですので、恐れ入りますが何卒よろしくお願いいたします。

管理シートは頂いたマクロのブックで実行しています。その間、他のブックは開いておりません。
確かに、フォルダ内に管理シートに入力しているファイルは目視で確認できるのですがマクロ実行すると
認識してもらえないようです。。。
ご教授頂いたように空白等ないようなファイル名に修正したりいろいろと試してはいるのですが
やはり存在しないと出てしまいます。。。
何度も申し訳ありません、他に何か考えられますか？

お礼日時：2017/06/05 16:10

No.35 回答者： tatsu99 回答日時： 2017/06/05 15:02

80行目以降を以下のようにしてください。

'売上明細ブック名設定
Uri_path = Jis_folder & "\" & Uri_book
MsgBox ("ファイル名<" & Uri_book & ">") '・・・・追加①
MsgBox ("フルパス名<" & Uri_path & ">") '・・・・追加②
If Dir(Uri_path) = "" Then
MsgBox (Uri_path & "が存在しません")
Exit Sub
End If

①と②を追加します。
①でファイル名を表示します。E２のセルと比較して下さい。
②でファイルのフルパスを表示します。　これが、間違いなく存在するか確認してください。

この回答へのお礼

再度、ご教授頂きましてありがとうございます。
マクロ追加の上、ファイル名、フルパスを管理シートと参照したのですが
やはり存在しないと出てしまいます。。。

お礼日時：2017/06/05 16:00

No.34 回答者： tatsu99 回答日時： 2017/06/05 14:31

＞ファイルもファイル名をコピーしているので存在しているはずなのですが。

。。やはりエラーになってしまいます。

ファイル名を空白がまじる余地のない簡単なファイル名にリネームして確認してみてください。
ABC.xlsx 等です。
E2でファイル名を指定しますが、その先頭に空白がないことも確認してください。
それでもエラーが出るようでしたら、再度、補足ください。

この回答へのお礼

度々、恐縮です。
>ファイル名を空白がまじる余地のない簡単なファイル名にリネームして確認してみてください。
>ABC.xlsx 等です。
>E2でファイル名を指定しますが、その先頭に空白がないことも確認してください。
>それでもエラーが出るようでしたら、再度、補足ください。
→やはり、エラーが出てしまいます。。。

お礼日時：2017/06/05 14:47

No.33 回答者： tatsu99 回答日時： 2017/06/05 14:16

＞→上記で実行しようとしたところ、早速エラーが出てしまい「ファイルが存在しません」と出てしまいました。

ちなみに、テストとして「 【実績】売上明細データ(201601).xlsx」というファイルを「実績」というフォルダに置き
＞実績フォルダ名はそのフォルダが格納されているアドレスを、実績ファイル名は上記名で、「計画」というフォルダには
＞集計先の支店のファイルを格納しているアドレスを「管理」シートへ例と同じように入力したのですがエラーが出てしまいます。
＞原因は何が考えられますでしょうか？
回答：実績フォルダ名は、フルパスで記入されていますか？
C:\AAA\BBB のように
ドライブ名まで、含めてください。
もし、実績フォルダに　\\AAA\BBB等のネットワークドライブを指定している場合は、念のため
あなたの個人の、Cドライブ等に実績フォルダを作成し、そこで確認していただけますでしょうか。

・・・・が存在しません。
と表示されますが、・・・・の部分は本当に実在しますか。
（ファイル名に空白が含まれている等の誤りはありませんか）


＞→承知いたしまた。
＞ちなみに、表示の件ですが元データ上は2017/1/21になっておりますし、ピボッドも元は2017/1/21ですが
＞月だけで表示しているので2017/1/21という表示にしたほうが確実なのでしょうか？
回答：複数年度のデータが格納されていなければ問題ありません。
2017/1/21のデータと2016/1/21のデータがあり、そのどちらもピボットで1月と表示しているなら、
両方のデータが集計対象となります。

この回答へのお礼

早速のご返信ありがとうございます。

①>回答：実績フォルダ名は、フルパスで記入されていますか？
>C:\AAA\BBB のように
>ドライブ名まで、含めてください。
>もし、実績フォルダに　\\AAA\BBB等のネットワークドライブを指定している場合は、念のため
>あなたの個人の、Cドライブ等に実績フォルダを作成し、そこで確認していただけますでしょうか。
>・・・・が存在しません。
>と表示されますが、・・・・の部分は本当に実在しますか。
>（ファイル名に空白が含まれている等の誤りはありませんか）
→おっしゃるようにCドライブで検証している為、フォルダ名はドライブから記載しております。
ファイルもファイル名をコピーしているので存在しているはずなのですが。。。やはりエラーになってしまいます。

②>回答：複数年度のデータが格納されていなければ問題ありません。
>2017/1/21のデータと2016/1/21のデータがあり、そのどちらもピボットで1月と表示しているなら、
>両方のデータが集計対象となります。
→本件に関しては、承知いたしました。現時点では、月が重複しておりませんので問題ありません。
データ蓄積していく等、変更があった場合にはご教授頂いたように年度表示にして対応するようにいたします。
ありがとうございます。

お礼日時：2017/06/05 14:26