ファイル名を変更後、コピーができない

質問

いつもお世話になります。

システムからダウンロードしたファイルを「東海」に変更して、H、AR、AS列にあるデーターを
「集計表」のN、O、P列に貼り付けしたいのですが、データーはあるのに、そのデーターが張り付きません。
どこが間違っているのか教えてください。よろしくお願いします。

「集計表」は、別の地域のデーターがN：Pに入っていいます。
　　　　　Ａ：Ｌは、市町村ごとに集計したデーターが入っています。
「東海」にファイル名を変更したのは、一度に貼り付けできなかったからです。
ファイル名を変更しなくても、「集計表」に貼り付けできたらうれしいです。

Sub 東海登録状況()
Dim t As Long
Dim i As Long
Dim bk As Workbook
Dim bk1 As Workbook
Dim sh As Worksheet
Dim sh1 As Worksheet

'ブック名の変更
ActiveWorkbook.SaveAs Filename:="東海.xls"　　←　この場所に「東海」とうファイルが
　　　　　　　　　　　　　　　　　存在します。置き換えますか？　　という表示がでます。
Set bk = Workbooks("東海.xls")
Set sh = bk.Worksheets(1)
Set bk1 = Workbooks("集計表.xlsx")
Set sh1 = bk1.Worksheets(1)

'シート名の変更
sh.Name = Format(Now, "mmdd")

t = sh.Range("A" & Rows.Count).End(xlUp).Row
i = sh1.Range("P" & Rows.Count).End(xlUp).Row

'必要な列を選択する

sh.Range("H2:H" & t).Copy Destination:=sh1.Range("N" & i + 1)
sh.Range("AR2:AS" & t).Copy Destination:=sh1.Range("O" & i + 1)

End Sub

回答No.1

chie65536（@chie65535） · ベストアンサー

＞ActiveWorkbook.SaveAs Filename:="東海.xls"

これは「マクロを走らせている現在のブックを、"東海.xls"と言う名前でセーブする」と言う意味です。

なので「貴方が別途用意した"東海.xls"とファイルは、上書きして失われてしまいます」ので「東海.xlsに入っているデータが貼り付かない」のは当たり前です。

上書きして失われてしまうから、確認の為に

＞この場所に「東海」とうファイルが存在します。置き換えますか？
＞という表示が

出るのです。

マクロを、以下のように変更してみましょう。

Sub 東海登録状況()
Dim t As Long
Dim i As Long
Dim bk As Workbook
Dim bk1 As Workbook
Dim sh As Worksheet
Dim sh1 As Worksheet

'東海のブックを開く
Workbooks.Open "東海.xls"

'集計表のブックを開く
Workbooks.Open "集計表.xlsx"

'東海.xlsのオブジェクトを設定
Set bk = Workbooks("東海.xls")
Set sh = bk.Worksheets(1)

'集計表.xlsxのオブジェクトを設定
Set bk1 = Workbooks("集計表.xlsx")
Set sh1 = bk1.Worksheets(1)

'東海.xlsのシート名を変更
sh.Name = Format(Now, "mmdd")

t = sh.Range("A" & Rows.Count).End(xlUp).Row
i = sh1.Range("P" & Rows.Count).End(xlUp).Row

'必要な列を選択する
sh.Range("H2:H" & t).Copy Destination:=sh1.Range("N" & i + 1)
sh.Range("AR2:AS" & t).Copy Destination:=sh1.Range("O" & i + 1)

'東海.xlsの変更を保存して閉じる（シート名の変更を反映させる）
bk.Close SaveChanges:=True

'集計表.xlsxの変更を保存して閉じる（コピーしたデータを反映させる）
bk1.Close SaveChanges:=True

'使用したオブジェクトを開放する
Set sh = Nothing
Set sh1 = Nothing
Set bk = Nothing
Set bk1 = Nothing

End Sub

なお、このマクロは「集計表.xlsxでもなく、東海.xlsでもない、別のワークブックに存在している事を想定」しています。

このマクロが「集計表.xlsxに存在する」のであれば、以下のようにします。

Sub 東海登録状況()
Dim t As Long
Dim i As Long
Dim bk As Workbook
Dim bk1 As Workbook
Dim sh As Worksheet
Dim sh1 As Worksheet

'東海のブックを開く
Workbooks.Open "東海.xls"

'集計表のブックを開くのを実行しない
'Workbooks.Open "集計表.xlsx"

'東海.xlsのオブジェクトを設定
Set bk = Workbooks("東海.xls")
Set sh = bk.Worksheets(1)

'集計表.xlsxのオブジェクトを設定
Set bk1 = Workbooks("集計表.xlsx")
Set sh1 = bk1.Worksheets(1)

'東海.xlsのシート名を変更
sh.Name = Format(Now, "mmdd")

t = sh.Range("A" & Rows.Count).End(xlUp).Row
i = sh1.Range("P" & Rows.Count).End(xlUp).Row

'必要な列を選択する
sh.Range("H2:H" & t).Copy Destination:=sh1.Range("N" & i + 1)
sh.Range("AR2:AS" & t).Copy Destination:=sh1.Range("O" & i + 1)

'東海.xlsの変更を保存して閉じる（シート名の変更を反映させる）
bk.Close SaveChanges:=True

'集計表.xlsxの変更を保存して閉じるのを実行しない
'bk1.Close SaveChanges:=True

'使用したオブジェクトを開放する
Set sh = Nothing
Set sh1 = Nothing
Set bk = Nothing
Set bk1 = Nothing

End Sub

要は「マクロが集計表.xlsx自身にあるので、集計表.xlsxを開いたり閉じたりしない」という変更を加えただけです。

お礼

'ブック名の変更
ActiveWorkbook.SaveAs Filename:="東海.xls"

これは、個人用マクロブックが「東海.xls」になったようです。
システムからダウンロードしたファイルをクリックしてからマクロを動かしているのですが、どうしてなのでしょうか？

補足

chie65535さん、早々にありがとうございます。

「東海.xls」は、システムからダウンロードした、長い名称のファイル（システムの名前＆ダウンロードした年月日＆時間）を
今回のマクロで「東海.xls」に変更したものです。
毎月２回、システムからダウンロードするのですが、毎回ファイル名が違うので、「東海.xls」に変更すると、マクロひとつで貼り付けができると考えました。
なので、オープンする「東海.xls」はありません。

'ブック名の変更
ActiveWorkbook.SaveAs Filename:="東海.xls"
ThisWorkbook.Save
と、上書き保存してみたのですが、データーが貼りつきません。
引き続き、よろしくお願いします。

あと、マクロは、個人用マクロブックに保存しています。