エクセルマクロ シート名称と他のファイル間の操作について

質問

シート名称に、「ＮＹ（１）」というように、（）を使っています。
多分この（）が原因だと思われますが、
Dim sh1 As worksheet
Set sh1 = worksheets("ＮＹ（１）"）
と定義しても、うまく作動しません。
「実行時エラー”９”　インデックスが有効範囲にありません。」
というメッセージが出てしまいます。
私の要望は、sheets（"データ")上のコマンドボタンから、sheets("ＮＹ（１）"）にあるプログラム（例えばコピーなど）を実行したいものです。
こういった場合、どのような定義？構文を組めば作動するでしょうか？
次に、このブック（ブック名＝ＮＹ１００）から（ブック名＝ＮＹ２００）のデータを操作する場合、（前者のブックにあるデータを、後者のブックに複写して、前者のコマンドボタンを押すことによって、後者のプログラム（例えばコピーなど）を実行させる）には、どのような定義？構文を組めば作動するでしょうか？
どなたか教えてください。
ちなみに、エクセル２００３、ＯＳ＝ＸＰです。

回答No.1

nishi6 · ベストアンサー

下記、
CommandButton1_Clickは、
　　Workbooks("ＮＹ１００.xls")のWorksheets("データ")の"C3:E8"を、
　　同じブックのWorksheets("ＮＹ（１）")にコピーする例です。

CommandButton2_Clickは、
　　Workbooks("ＮＹ１００.xls")のWorksheets("データ")の"C3:E8"を、
　　Workbooks("ＮＹ２００.xls")のWorksheets("ＮＹ（２）")にコピーして、

　　Workbooks("ＮＹ２００.xls")のWorksheets("ＮＹ（２）")を、
　　Workbooks("ＮＹ２００.xls")のWorksheets("ＮＹ（２）ＣＯＰＹ")にコピーする例です。
　　　２つのブックとも開いておく必要があります。

※「実行時エラー”９”　インデックスが有効範囲にありません。」のエラーは
　シート名を間違えている場合もありますが、ボタンを使ってマクロを起動している場合、
　シートが選択されない（フォーカスがボタンにある）状態がよく起きます。
　　　コードに『ActiveCell.Activate』を挿入するか、
　　　ボタンのプロパティ『TakeFocusOnClick』をFalseにしてみて下さい。

CommandButton1、CommandButton2は、Worksheets("データ")に配置し、
下記コードはWorksheets("データ")のコードウインドウに書いています。

ご参考に。（Excel2000で確認）

Private Sub CommandButton1_Click()
　　'// シートの定義
　　Dim srcWS As Worksheet　　 '// コピー元ワークシート
　　Dim desWS As Worksheet　　 '// コピー先ワークシート
　　　　Set srcWS = Worksheets("データ")
　　　　Set desWS = Worksheets("ＮＹ（１）")

　　'// セルのコピー（C3:E8をコピー先シートの同じ位置にコピーする例）
　　srcWS.Range("C3:E8").Copy Destination:=desWS.Range("C3")
End Sub

Private Sub CommandButton2_Click()
　　'// ブックの定義
　　Dim srcWB As Workbook　　　　'// コピー元ブック
　　Dim desWB As Workbook　　 '// コピー先ブック
　　　　Set srcWB = Workbooks("ＮＹ１００.xls")
　　　　Set desWB = Workbooks("ＮＹ２００.xls")

　　'// シートの定義
　　Dim srcWS As Worksheet　　 '// コピー元ワークシート
　　Dim desWS1 As Worksheet　　 '// コピー先ワークシート１
　　Dim desWS2 As Worksheet　　 '// コピー先ワークシート２
　　　　Set srcWS = srcWB.Worksheets("データ")
　　　　Set desWS1 = desWB.Worksheets("ＮＹ（２）")
　　　　Set desWS2 = desWB.Worksheets("ＮＹ（２）ＣＯＰＹ")

　　'// ブック間でセルのコピー
　　srcWS.Range("C3:E8").Copy Destination:=desWS1.Range("C3")

　　'// コピー先ブックでのセルのコピー
　　desWS1.Range("C3:E8").Copy Destination:=desWS2.Range("C3")

End Sub

補足

早速のご教示、ありがとうございました。
全く問題無く作動しました。
すみませんが、もうひとつだけ教えて欲しいのですが、
別ブック間で「セルの値のみ」コピーする構文を教えていただけないでしょうか？
どうかよろしくお願いいたします。

回答No.2

nishi6

＞別ブック間で「セルの値のみ」コピーする構文を教えていただけないでしょうか？

値複写の場合は、回答例を書き換えると下記のようになります。
ブック間でセルのコピー　の場合、

　　srcWS.Range("C3:E8").Copy Destination:=desWS1.Range("C3")
　　　　　↓
　　srcWS.Range("C3:E8").Copy
　　desWS1.Range("C3").PasteSpecial Paste:=xlValues

お礼

大変助かりました。
問題が解決して作業を次へ進めることができます。
本当にどうもありがとうございました。