Excel VBAでシートのコピー、移動が時々おかしい

質問

Sheets(OldNendo & "_年提案進捗状況表と元データ").Copy after:=Sheets(OldNendo & "_年提案進捗状況表と元データ")

としていますが、
Worksheet クラスの Copy メソッドが失敗しました。

というエラーになってしまいます。

Sheets(OldNendo & "_年提案進捗状況表と元データ").Select
は実行すると正常に動作するのでシートの指定の仕方がおかしいわけではないとは思うんですが…。

よろしくお願いします。

回答No.4

R-Fuji · ベストアンサー

正直、これでできなければ私にはお手上げです。スミマセン。
下の例を説明しますと、Book1.xlsをいうファイルを作成し、第一シートに"test1"、第二シートに"test2"と見出しタブから名前を付け、"test1"にボタンを置き、そのクリックイベントです。

Private Sub CommandButton1_Click()
Dim xlBook As Workbook
Dim xlSheet1 As Worksheet
Dim xlSheet2 As Worksheet

'Book1.xlsは現在使用しているエクセルブック名（フルパスではありません）
Set xlBook = Workbooks("Book1.xls")

'コピー元のシート
Set xlSheet1 = xlBook.Worksheets("test1")
'コピー先の対象シート
Set xlSheet2 = xlBook.Worksheets("test2")

'コピー実行
xlSheet1.Copy after:=xlSheet2
End Sub

私の環境、OS:Win2000 EXCEL2000で動作を確認しました（以前のも）。
コピーができなければ、確認してください。
・シートの見出し名を間違っていないか（半角スペースなど注意）。
・ブックに保護（ロック）がかかっていないか。ヘッダーメニューの[ツール]>[保護]から確認。
・新規ブックを作成して試してみる。

上記のことを試してみてください。あとはちょっと想像がつきません。

お礼

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

私でさえ嫌気がさしてるのに（笑）何度もありがとうございます。

今新規ファイルを作成して、そちらに移行中です。
今のところ無事動いているようです。
少々様子を見ようと思います。
その間は締め切らずにいます。
また何かありましたら質問させていただきますのでよろしくお願いしますm(_ _)m

回答No.3

R-Fuji

すいません補足します。
前もって云いますと、見当が外れてると全く関係がないと思います。

EXCELのシート上でWindowオブジェクト（ボタンなど）を置けることはご存知だと思います。また、EXCELシート自体もオブジェクトだと考えてください。
VBAではオブジェクトを扱う時、表示しているオブジェクトのハンドル（IDみたいなもの）を取得して操作しています。逆に云えばハンドルが取得できなければ操作できないというわけです。
ここで、WindowオブジェクトとEXCELオブジェクトのハンドルが違うことがわかっています。

つまり、VBAのメソッドではActive状態のハンドルだけを取得して操作してますので、ボタンなどにフォーカス（カーソルの位置）が移っている状態だと、EXCELシートの操作ができないということ。さらにEXCEL固有のメソッド（命令文）が使えないということに繋がります。
実例を挙げると、Sort、Protected、Window操作（スクロールの表示非表示）などがボタンにフォーカスが付いたままだとエラーが発生します。
しかし、Sheet("test").Activeしてもボタンからはフォーカスが移りませんので（バグ？）、Cells(1,1).Activeなどすると解決します。

例）
Sheets("test").Activate
Sheets("test").Cells(1,1).Activate
Sheets("test").Copy after:=Sheets("test2")

尤も対象シート上にボタンなどのオブジェクトを設置していない場合については問題にならないのですがね(^_^;

尚、VBの参考サイトですが、EXCEL&WORD操作は、エクセルオブジェクトの宣言を少し変えてやればそのまま使えます。
dim xlsBooks as Workbooks
dim xlsSheet as Worksheets
で作れるエクセルオブジェクトを使えるようになれば色々試してみることができると思います。
http://www.bcap.co.jp/hanafusa/

お礼

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

＃２の回答をよんで「どこかのセルを指定すればOKってことかな？」と思いやってみたんですが、またしてもエラーに…。

今回もこのようにしてみました。

SheetName1 = OldNendo & "_年提案進捗状況表と元データ"
Sheets(SheetName1).Activate
Sheets(SheetName1).Cells(1, 1).Activate
Sheets(SheetName1).Copy after:=Sheets("表紙")

しかしこれまたエラーになってしまいました。
ボタンを作成し、それにコードを貼り付けてみたんですが、同じくエラーになってしまいました。。

回答No.2

R-Fuji

状況がいまいちわからないのですが、EXCEL VBAはバージョンによって動作仕様が違います。もちろんヘルプにも出ていません、MS的仕様です（苦笑）

経験則では、EXCEL97においてオブジェクトにフォーカスが移っている時Sheetのhandleが取れず、各メソッドが動作不能になることを確認しています。
オブジェクトにフォーカスが移ってるようであれば、Cellsに対してSelect又はActiveしてみては？

あと、OSのバージョン、EXCELのバージョンと状況を提示したほうがわかりやすいですよ（^ ^

お礼

回答ありがとうございます。
Excel2000、windows2000です。

＞オブジェクトにフォーカスが移ってるようであれば、Cellsに対してSelect又はActiveしてみては？
これはどういうことでしょうか？（＾＾ゞ
申し訳ありませんが、詳しく教えてください。よろしくお願いします。

補足

Sheets(OldNendo & "_年提案進捗状況表と元データ").Copy

というようにすると新規ブックにきちんとコピーが生成されるので、コピーもきちんと作動していると思います。
この後の表記をいろんなシート名にしてみたり、before/afterを変更したりしていますが、エラーばかりです。
時々ちゃんと動作したりもすることがあります。
Sheets(1)とかもしてみましたが、ダメでした。

回答No.1

nishi6

コピー元のシートがアクティブでない可能性があります。
また、ボタンをマクロ実行のきっかけにしている場合、ボタンのプロパティ『TakeFocusOnClick』をFalseにすれば正常に動作することもあります。

いずれにしても、下記のように、該当シートをアクティブにする命令を追加してみてください。

Sheets(OldNendo & "_年提案進捗状況表と元データ").Activate
Sheets(OldNendo & "_年提案進捗状況表と元データ").Copy after:=Sheets(OldNendo & "_年提案進捗状況表と元データ")

お礼

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

Sheets(OldNendo & "_年提案進捗状況表と元データ").Activate
Sheets(OldNendo & "_年提案進捗状況表と元データ").Copy after:=Sheets("表紙")
としてみましたが同じくエラーが出ました。

（今まで1行目にactivateではないですがselectということでシート選択を入れたりはしてました）

今のところ実行はMicfosoft VBから直接実行しています。