ワークブックAの指定領域をワークブックBの指定列の空いているセルの左から順にペーストされていくマクロを作りたい

前提・実現したいこと

ワークブックAのシートAのB5:B7をコピーしワークブックBのシートBのF32列の一番右の埋まっているセルの一つ右隣にペースト(マクロを起動するたびに右隣のセルにペーストされていく様な)するマクロを作りたいです。

発生している問題・エラーメッセージ

実行時エラー'1004'
アプリケーション定義またはオブジェクト定義のエラーです

該当のソースコード

' 処理高速化用：関数開始時に実行する処理セット
Function startFunc()
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
End Function
' 処理高速化用：関数終了時に実行する処理セット
Function endFunc()
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
End Function

' コピー処理



Function book2bookCopy(srcSheet As String, srcRange As String, _
                dstBook As String, dstSheet As String, dstRange As String)
    ' 処理高速化用：開始処理
    Call startFunc
    With Workbooks.Open("ワークブックB")
        ' コピー
        ThisWorkbook.Worksheets(srcSheet).Range(srcRange).Copy
        .Worksheets(dstSheet).Range(dstRange).PasteSpecial _
            xlPasteValuesAndNumberFormats
        ' コピー中状態を解除
        Application.CutCopyMode = False
        ' 処理高速化用：終了処理
        Call endFunc
        ' 保存して閉じる
        .Close True
    End With

End Function





' テスト実行用
Function test()
    Call book2bookCopy("シートA", "B5:B7", "ワークブックB", "シートB", "Range(A1).End(ToRight).Offset(1, 0).")
End Function

試したこと

Range(A1).End(ToRight).Offset(1, 0)を単一セル(F32等)に変更すると実行できたのでこの箇所が原因かと思われます。

補足情報（FW/ツールのバージョンなど）

Excel 2016 VBA

DreamTheater

2020/06/03 01:12

『F32列の一番右の埋まっているセルの一つ右隣～』Ｆ列ってことでしょうか？（F32は単一セルです）Ｆ列なら、なぜdstRangeにRange(A1).End(ToRight)...を指定しているのでしょうか？質問を見直して頂けますか？

行動規範の内容に同意します

回答1件

ベストアンサー

dstRangeに関数を含むものを文字列で渡したら、実際に使われるのは以下のようになるのでおかしいな、と思わないですかね。

VBA
1' これが
2.Worksheets(dstSheet).Range(dstRange).PasteSpecial _
3
4' こう展開される
5.Worksheets("シートB").Range("Range(A1).End(ToRight).Offset(1, 0).").PasteSpecial _

関数に渡すのは「どの行か」という情報だけでよいでしょう。
動作確認はしていませんが、次のような感じでよいはずです。
尚、せっかく渡したdstBookを使っていなかったのでOpen時に使うようにしました。
またToRightをxlToRightに、Offsetの引数を(1,0)から(0,1)に修正しています。

VBA
1Function book2bookCopy(srcSheet As String, srcRange As String, _
2                dstBook As String, dstSheet As String, dstRange As String)
3    ' 処理高速化用：開始処理
4    Call startFunc
5    With Workbooks.Open(dstBook)
6        ' コピー
7        ThisWorkbook.Worksheets(srcSheet).Range(srcRange).Copy
8        .Worksheets(dstSheet).Range(dstRange).End(xlToRight).Offset(0, 1).PasteSpecial _
9            xlPasteValuesAndNumberFormats
10        ' コピー中状態を解除
11        Application.CutCopyMode = False
12        ' 処理高速化用：終了処理
13        Call endFunc
14        ' 保存して閉じる
15        .Close True
16    End With
17
18End Function
19
20' テスト実行用
21Function test()
22    Call book2bookCopy("シートA", "B5:B7", "ワークブックB", "シートB", "A1")
23End Function