Excelマクロ アクティブブックの使い方等多数質問がありますので御教授お願いします。

仕事で急遽Excelマクロを組む必要が出てきたのですが、インターネットで情報を見ながらマクロ記録を使ったりして初めてマクロを組んでいます。
思うようにマクロが組めないので御教授頂けないしょうか？

Sub 予定表作成()
' 予定表作成 Macro
ChDir "C:\Users\○○○\Desktop"
Workbooks.Open(Filename:="C:\Users\○○○\Desktop\元データブックA.xls"). _
RunAutoMacros Which:=xlAutoOpen
Sheets("予定表_").Select
Sheets("予定表_").Copy After:=Workbooks("予定作成プログラム.xlsm").Sheets(1)
Workbooks.Open(Filename:="C:\Users\○○○\Desktop\元データブックB.xls") _
.RunAutoMacros Which:=xlAutoOpen
Sheets("予定表_").Select
Sheets("予定表_").Copy After:=Workbooks("予定作成プログラム.xlsm").Sheets(1)
Sheets("予定表_").Select
Sheets("予定表_").Name = "予定表1"
Sheets("予定表_ (2)").Select
Sheets("予定表_ (2)").Name = "予定表2"
Sheets("Sheet1").Select
Windows("元データブックA.xls").Activate
ActiveWorkbook.Close
Windows("元データブックB.xls").Activate
ActiveWorkbook.Close
End Sub

下記、分かりにくい文章が多々あると思いますが、御教授頂けると幸いです。

他部門から入手した元データブックAと元データブックBを毎月入手します。
元データブックAと元データブックBがあります。
ブックAとブックBは同じ書式です。
ブックAの件数行数が約600行、ブックBが約30行の構成です。

新たなExcelマクロブックを作成して、マクロブックのsheet1にはマクロを動かすためのボタンを配置しています。
上記、元データブックAをsheet2へコピーはできます。

しかし、ブックBをsheet2のブックAデータの下へ続けてコピーしたいのですが、マクロ指令がよく分かりません。

また、開いた元データブックAとBをマクロで閉じることができません。(理由は質問2を参照下さい。)

sheet2にコピーしたデータはそのままでは空欄が多々有り、使用できないので修正が必要になります。

例えば、添付ファイルの課名欄の四課部分ですが、フィルター機能を使って四課のみを表示させようとしますと名称欄が空欄となり、行先が見えなくなってしまいます。

【質問1】
ブックBをマクロブックのsheet2ブックAデータに続けてコピーするにはどの様にすればよろしいでしょうか？

【質問2】
アクティブブックがコピーした先のブックとなり、元データのブックでなくなっているため、毎月変わるブックを選択する方法が分かりません。どのようにやればよろしいでしょうか？

【質問3】
青色枠の名称欄セルE3「○○会社」とセルE4「△△営業所」をセルE3に「○○会社␣△△営業所」および定格/型番セルF3「□□」とセルF4「￥￥」をセルF3に「□□/￥￥」と表示させることはできないでしょうか？

【質問4】
赤枠の空欄E4:E9に名称は「○○会社␣△△営業所」およびF4:F9に定格/型番は「□□/￥￥」と次の文字があるところまでコピーすることはできないでしょうか？

【質問5】
質問3と質問4の内容を下の約600行+約30行まで行いたいのですが、どの様にでしょうか？

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

• 回答ありがとうございます。
  
  仕事の流れですが、他部門からメールで毎月フォーマットの同じ2つのxlsファイルが送られてきます。
  このファイルでは自部門で管理しているxlsファイルの表と異なります。
  最終的な狙いは送られてきた2つのxlsファイルを自部門だけ抽出して、自部門で管理しているファイルへ落とし込みたいと思っています。
  ですが、一度に自部門の表までできないと思い、他部門からのファイル加工までの質問させて頂きました。

  No.1の回答に寄せられた補足コメントです。 補足日時：2016/01/24 09:21

• 【質問2】についてですが、他部門から送られてくる2つのファイルをそれぞれブックA、ブックBとします。新に作ったマクロのファイルをブックCとします。
  ブックAを開いてシートをブックCにコピーさせるとアクティブがブックCになります。
  ブックAはコピーしてしまえば、必要なくなるのでファイルを閉じたいのですが、アクティブでなくなってしまいます。
  ブックBの時も同様になります。
  毎月同じブック名であれば問題ないのですが、毎月異なるファイル名の指定方法が分からないので、どの様に指定するのがいいのかという意味でした。

    補足日時：2016/01/24 09:24

No.4 ベストアンサー 回答者： hallo-2007 回答日時： 2016/01/24 14:00

取りあえず。

一案です。
先に紹介したように、関数で空白部分を埋めた取り込みシートを準備
N1セルに　=COUNT(予定表!I:I) とでも入れてデータの件数を計算させておく。
マクロのファイルを特定のフォルダに保存
メールでブックAとブックBが添付されて来る。
添付のブックAを手作業で、マクロのファイルと同じフォルダに名前を付けて保存。
その際に、名前は取込データとする。
次からは、それぞれのファイルへ上書きして保存する事になる。
マクロのファイルには、データ　というシートを準備
1行目A～Kに項目名を入れておく。
実行するマクロ
Sub 予定表作成()
' 予定表作成 Macro
Sheets("データ").Select
GYOU=Range("G" & Rows.Count).End(Xlup).Row+1
'蓄積されているデータの最終行の一つ下の行番号を取得
Workbooks.Open(Filename:=ThisworkBookPath & "\取込データ.xls")
Sheets("取り込みシート").Select
KENSU=Rnage("N1").Value
ThisWorkBook.Sheets("データ").Range("A" & GYOU & ":K" & GYOU+KENSU).Value=Range("A3:K" & KENSU+3).Value
Sheets("予定表").Rnage("A3:M1000").ClearContents
ActiveWorkbook.Save
'ボタン操作ミスで重複取込防止のために取り込んだら空白にして保存
ActiveWorkbook.Close
Sheets("元のシート").Select
End Sub
ボタンをクリックするとデータのシートに値が入る。

メールを操作してブックBを取込データのファイルへ上書きして保存する。
マクロを実行
データのシートの下の行に値が入るはずです。
実証はしていませんのでスペルミスなどあれば修正しながら活用してみてください。

>毎月異なるファイル名の指定方法が分からないので
同じフォルダに同じ名前で上書きして使うと良いです。
コピーではなく
ThisWorkBook.Sheets("データ").Range("A" & GYOU & ":K" & GYOU+KENSU).Value=Range("A3:K" & KENSU+3).Value
指定した範囲の値を入れるというやり方です。

この回答へのお礼

連絡が遅くなってしまい申し訳ありません。
御教示頂いた内容で確認しながらマクロの動作を確認しました。
ほぼ思い通りにできました。
ご丁寧に回答頂きましてありがとうございました。

お礼日時：2016/03/21 20:53

No.3 回答者： hallo-2007 回答日時： 2016/01/24 10:29

質問３の解決案ですが

＞他部門からのファイル加工までの質問させて頂きました。
送付されるファイルのデータ取り込み用のシートを追加することは可能でしょうか？
仮に現在のシート名を入力用シートとして
新たに、取り込み用シートという名前でシートを追加
A3、B3セルは
=IF(入力用シート!A3<>"",入力用シート!A3,A2)　
=IF(入力用シート!B3<>"",入力用シート!B3,B2)　
C3,D3セルに
=IF(AND(入力用シート!E3<>"",入力用シート!E4<>""),入力用シート!E3&入力用シート!E4,C2)
=IF(AND(入力用シート!F3<>"",入力用シート!F4<>""),入力用シート!F3&入力用シート!F4,C2)
E3より右は
＝入力用シート!G3&""　と入れて右へコピー
3行目の式をすべて選択して、下方向へ1000行分九位コピー
空白の部分が埋められたシートが出来ませんでしょうか。
このシートは、保護をかけるなり、非表示にするなりして配布します。
以後は、このシートの値を取り込むようにVBAを作成するという手順は
可能でしょうか。

No.2 回答者： hallo-2007 回答日時： 2016/01/24 10:09

No1です。

【質問2】の意味がわかりました。
Activeでなくなった以上、名前で指定するしかないです。
Windows("元データブックA.xls").Activate
ActiveWorkbook.Close
は
Workbook("元データブックA.xls").Close
とまとめることができます。
それとマクロを起動しているブックは
ThisWorkbookと指定することができます。
Sheets("予定表_").Copy After:=ThisWorkbook.Sheets(1)
で大丈夫ですね。
＞他部門からメールで毎月フォーマットの同じ2つのxlsファイルが送られてきます。
は、一つの部署から一回のメールに２つのファイルが添付でしょうか。
或いは、部署は2つしかないという事でしょうか。

No.1 回答者： hallo-2007 回答日時： 2016/01/24 00:06

＞仕事で急遽Excelマクロを組む必要が出てきたのですが

と
＞初めてマクロを組んでいます。
では、結構敷居が高いです。

そもそも仕事の流れは？

【質問2】
アクティブブックがコピーした先のブックとなり、元データのブックでなくなっているため、毎月変わるブックを選択する方法が分かりません。
意味がわかりません。

メールで他部門からデータが送られてくるのを一枚のシートにまとめたい。
と云うのであれば
１、メールに添付されたファイルを名前を付けて保存
２、常に同じフォルダー、同じファイル名で上書き保存を実行
３、マクロを組み込んだファイルからマクロを実行
４、上書きしたファイルからデータをコピー
５、上書きしたファイルのデータ部分を全て空白にして保存、終了
次のBファイルも同様の手順で、同じファイル名で上書き保存
１～５の操作を毎月行う。
上記の操作では無理でしょうか？