マクロブック内のあるシートのセルを違うブックのあるシートへ貼り付けたい

VBAを勉強したく、練習しています。
現在操作しているマクロ有効ブックの「元シート」のＡ～Ｇ列を、貼り付け先.xlsxの「貼り付け先シート」のA~G列へ値貼付したいのですが、うまくいきません。
（2つのファイルは同じフォルダにファイルは保存されている）

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
Dim　WS1 As Worksheets, WS2 As Worksheets

Set WS1 = ThisWorkbook.Worksheets("元シート")
Set WS2 = Workbooks(”貼り付け先.xlsx”).Worksheets("貼り付け先シート")

WS1.Range("A:G").Copy
Workbooks.Open ThisWorkbook.Path & "貼り付け先.xlsx"
WS2.Range("A:G").PasteSpecial xlPasteValues

Workbooks("貼り付け先.xlsx").Close SaveChanges:=True

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

このままだと「型が一致しません」とエラーがでてしまいます。

As Worksheet　に変更→「インデックスが有効範囲ではありません」
As Sheets に変更　→「メゾットまたはデータメンバーが見つかりません」

というエラがでてしまいます。

WS1,WS2と定義せず、全部入力すると動作するのですが、なぜこれだとうまくいかないのでしょうか？

知識不足で大変恐縮なのですが、わかる方いらっしゃったら教えていただけないでしょうか？

No.2 ベストアンサー 回答者： WindFaller 回答日時： 2018/09/30 20:46

すでに解答が出ていますが、私からコメントを入れておきます。

>worksheetとworksheetsの違いがまだよくわからない

英語の単数と複数のようなものですが、Worksheets というのは、コレクションといって、複数同類のオブジェクトが集まっているものを指し示します。シートひとつを選んだのですから、Worksheetsにはなりませんね。

似たようなものに、Windows と Window というものがあります。
Cells は、なぜか複数集まってもいないのに、s がついていますが、たぶん、製作者が何かのミスか、もしかしたら、製作途中で気が変わったのかもしれません。
しかし、Sheets はあるけれども、Sheetはありませんね。Sheet1,Sheet2 があるけれども、よほどのことがない限りは使いません。この辺りは、忘れてしまったほうが良いかもしれません。

私も念のためにコードを残しておきます。
'//
Sub TestMacro()
　Dim Ws1 As Worksheet, Ws2 As Worksheet
　Dim Wb2 As Workbook
　Dim fn As String

　fn = "貼り付け先.xlsx"　　'全部を書き直さなくても済みます。

　Set Ws1 = ThisWorkbook.Worksheets("元シート")
　Set Wb2 = Workbooks.Open(ThisWorkbook.Path & "\" & fn) '変数に確保
　Set Ws2 = Wb2.Worksheets("貼り付け先シート")
　Ws1.Range("A:G").Copy 'コピー
　Ws2.Range("A:G").PasteSpecial xlPasteValues 'ペースト
　Wb2.Close SaveChanges:=True
End Sub

ブックは開いた時に、変数に入れられますから、そこでオブジェクトを確保します。
コピーとペーストの間は、短いほうがよいです。途中で、インタラクト(割り込み・妨害)が入ることがあるからです。

このコードの最後に、Set Wb2 = Nothing: Set Ws1 = Nothing などは入れるべきか？
基本的には、単独のプロシージャでは問題はありません。マクロが終わると同時に開放されています。

この回答へのお礼

詳細なコメントなうえに、きれいに整頓していただいてありがとうございます。
私が指定してるのは一つのシートのことだからsはいらないんですね。
ブックは開いた時に変数に入れられる、というのも知らなかったです。
とても勉強になりました！

お礼日時：2018/09/30 21:22

No.1 回答者： tatsu99 回答日時： 2018/09/30 15:35

以下のようにしてください。

①
Dim WS1 As Worksheets, WS2 As Worksheetsではなく
Dim WS1 As Worksheet, WS2 As Worksheet　です。
②
Set WS1＝
Set WS２＝
が有効となるのは、そのブックがオープンされている場合のみです。
③
Workbooks.Open ThisWorkbook.Path & "貼り付け先.xlsx"
ではなく
Workbooks.Open ThisWorkbook.Path & "\" & "貼り付け先.xlsx"
です。（￥が必要です）

--------------------------------------------
Public Sub 貼り付け()
Dim WS1 As Worksheet, WS2 As Worksheet

Set WS1 = ThisWorkbook.Worksheets("元シート")

WS1.Range("A:G").Copy
Workbooks.Open ThisWorkbook.Path & "\" & "貼り付け先.xlsx"
Set WS2 = Workbooks("貼り付け先.xlsx").Worksheets("貼り付け先シート")
WS2.Range("A:G").PasteSpecial xlPasteValues

Workbooks("貼り付け先.xlsx").Close SaveChanges:=True

End Sub

この回答へのお礼

早速ご回答いただきありがとうございます。
ご指示通り直してみたらうまく動作しました！
setのタイミングは大事なのですね…。
worksheetとworksheetsの違いがまだよくわからないのですが、もう少し勉強します。

お礼日時：2018/09/30 16:14