Excel VBA 非表示の別ブックへシートコピー

質問

Excel2010のVBAで、別のExcelブックを非表示で開いて、
シートをコピーすると、
「実行時エラー'1004':WorksheetクラスのCopyメソッドが失敗しました。」
というエラーが出て、正しくシートをコピーすることができません。

(1)のように自分のブックへはシートをコピーすることはできるのですが、
(2)のように別のExcelブック上でシートをコピーする場合と
(3)のように別のExcelブック上にシートをコピーする場合の
いずれも同様のエラーになります。

どのように記述すれば(2)と(3)でもコピーすることができるのでしょうか。

-------------------------------------------------------------
Sub test()

　Dim newEx As Excel.Workbook
　Dim newFile As String

　newFile = ThisWorkbook.Path & "\New_Book.xlsx"
　Set newEx = Workbooks.Open(newFile, UpdateLinks:=0)

　Application.Windows("New_Book.xlsx").Visible = False

　'(1)New_BookのSheet3を自分のブックにコピーする (正常)
　newEx.Worksheets("Sheet3").Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

　'(2)New_BookのSheet3をNew_Bookにコピーする (エラー)
　newEx.Worksheets("Sheet3").Copy after:=newEx.Sheets(newEx.Sheets.Count)

　'(3)自分のブックのSheet3をNew_Bookにコピーする (エラー)
　ThisWorkbook.Worksheets("Sheet3").Copy after:=newEx.Sheets(newEx.Sheets.Count)

　Application.Windows("New_Book.xlsx").Visible = True

　Application.DisplayAlerts = False
　newEx.Save
　newEx.Close
　Application.DisplayAlerts = True
　Set newEx = Nothing

End Sub
-------------------------------------------------------------

回答No.2

ushi2015

こんにちは
タスクバーのちらつきを抑えるなら別インスタンスで開けばいいのですが、
(1)、(3)がエラーになってしまうので、シートを追加してセルをコピーにしてみました。
ダメでしょうか？
Sub test1()
Dim newEx As Workbook
Dim newFile As String
Dim objEX As Object
Dim tmpS As Worksheet

Set objEX = CreateObject("Excel.Application")

newFile = ThisWorkbook.Path & "\New_Book.xlsx"
Set newEx = objEX.Workbooks.Open(newFile, UpdateLinks:=0)

'(1)New_BookのSheet3を自分のブックにコピーする ()
Set tmpS = ThisWorkbook.Worksheets.Add
tmpS.Move , ThisWorkbook.Worksheets(ThisWorkbook.Sheets.Count)
newEx.Worksheets("Sheet3").Cells.Copy
tmpS.Paste
Set tmpS = Nothing

'(2)New_BookのSheet3をNew_Bookにコピーする ()
newEx.Worksheets("Sheet3").Copy after:=newEx.Sheets(newEx.Sheets.Count)

'(3)自分のブックのSheet3をNew_Bookにコピーする ()
Set tmpS = newEx.Worksheets.Add
tmpS.Move , newEx.Worksheets(newEx.Sheets.Count)
ThisWorkbook.Worksheets("Sheet3").Cells.Copy
tmpS.Paste
Set tmpS = Nothing

Application.DisplayAlerts = False
newEx.Save
newEx.Close
objEX.Quit
Application.DisplayAlerts = True
Set newEx = Nothing
Set objEX = Nothing

End Sub

お礼

教えていただきました方法で検討していましたが、
シートコピーをセルのコピーで行うと、
セルに付けられているフィルタなどが
正しくコピーされないということが分かり
この方法ではちょっとまずいです。

あれから時間が経過していますので、
ここで一旦クローズして、
(2)(3)でなぜ駄目なのかも含めて、
改めてご質問したいと思います。

いろいろな検討していただきまして
ありがとうございました。

補足

こんにちは。

教えていただきました方法で、
タスクバーのちらつきもなく、
正しくコピーすることができました。

いろいろな検討していただきまして
ありがとうございました。

別インスタンスで開くと、(1)、(3)が
エラーになってしまうのは、おそらく、
Excelアプリケーションを1つ起動して、
この中で開いた2つのブック間では、
シートのコピーはできるのですが、
Excelアプリケーションを2つ起動して、
異なるExcelアプリケーションのブック間では、
シートのコピーができないことが
原因なのではないかと思われます。

また、今回、教えていただきました別インスタンスで
開く方法で、いろいろ調べていたのですが
自分のブックと別ブックのシートのコピーではなく、
別ブックと別ブックでのシートのコピーを行ってみると、
(1)(2)(3)すべて正しく行えることが分かりました。
(下記 Test2)

であるならば、最初の例の(2)(3)でも、
正しく行えるように思われるのですが、
なぜ駄目なのかこの原因が知りたいです。

------------------------------------------------
Sub test2()

　Dim objEX As Excel.Application
　Dim oldEx As Workbook
　Dim oldFile As String
　Dim newEx As Workbook
　Dim newFile As String

　Set objEX = CreateObject("Excel.Application")

　'objEX.Visible = True '動作確認用

　oldFile = ThisWorkbook.Path & "\Old_Book.xlsx"
　Set oldEx = objEX.Workbooks.Open(oldFile, UpdateLinks:=0)

　newFile = ThisWorkbook.Path & "\New_Book.xlsx"
　Set newEx = objEX.Workbooks.Open(newFile, UpdateLinks:=0)

　'(1)New_BookのSheet3をOld_Bookにコピーする <正常>
　newEx.Worksheets("Sheet3").Copy after:=oldEx.Worksheets(oldEx.Sheets.Count)

　'(2)New_BookのSheet3をNew_Bookにコピーする <正常>
　newEx.Worksheets("Sheet3").Copy after:=newEx.Worksheets(newEx.Sheets.Count)

　'(3)Old_BookのSheet3をNew_Bookにコピーする <正常>
　oldEx.Worksheets("Sheet3").Copy after:=newEx.Sheets(newEx.Sheets.Count)

　Application.DisplayAlerts = False
　oldEx.Save
　newEx.Save
　Application.DisplayAlerts = True
　oldEx.Close
　newEx.Close
　objEX.Quit
　Set oldEx = Nothing
　Set newEx = Nothing
　Set objEX = Nothing

End Sub
------------------------------------------------

回答No.1

ushi2015

こんにちは
Application.Windows("New_Book.xlsx").Visible = False
はやめて、
Application.ScreenUpdating = False
にしてはどうですか？
Sub test()
Dim newEx As Excel.Workbook
Dim newFile As String
Application.ScreenUpdating = False
newFile = ThisWorkbook.Path & "\New_Book.xlsx"
Set newEx = Workbooks.Open(newFile, UpdateLinks:=0)

' Application.Windows("New_Book.xlsx").Visible = False

'(1)New_BookのSheet3を自分のブックにコピーする (正常)
newEx.Worksheets("Sheet3").Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

'(2)New_BookのSheet3をNew_Bookにコピーする (エラー)
newEx.Worksheets("Sheet3").Copy after:=newEx.Sheets(newEx.Sheets.Count)

'(3)自分のブックのSheet3をNew_Bookにコピーする (エラー)
ThisWorkbook.Worksheets("Sheet3").Copy after:=newEx.Sheets(newEx.Sheets.Count)

' Application.Windows("New_Book.xlsx").Visible = True

Application.DisplayAlerts = False
newEx.Save
newEx.Close
Application.DisplayAlerts = True
Set newEx = Nothing
Application.ScreenUpdating = True

End Sub

補足

ushi2015さん。こんにちは。

回答ありがとうございます。

今回ご質問しました部分のコードは、
外部ファイルを操作している部分のみで、
全体のコードでは、最初と(最後)の部分に、
Application.ScreenUpdating = False(True)
は入れてあるのですが、
これだけでは、開いたExcelブックのアイコンが
タスクバーに表示されています。
サンプルでは1つのExcelファイルだけ開いていますが、
実際には、開くExcelファイルも複数で、順番に、
開いたり閉じたりを頻繁に繰り返す処理になっていて、
実行中(数十分間)の間ずっと、
タスクバーの部分がちらちら動いていて、
大変見苦しいので非表示にしたかったのです。

説明不足で申し訳ありません。

よろしくお願いします。