[[20211203194225]]　『マクロで値をコピーして別のブックに貼り付け』（もち）　ページの最後に飛ぶ

『マクロで値をコピーして別のブックに貼り付け』（もち）

VBAについて。値をコピーして他のブック(ここでは集計表)に値を貼り付けます。
集計表は閉じている前提で作成しています。

 ・貼り付け先のブックを既に開いている場合はそのまま使用し貼り付け、
　貼り付け後もクローズしない。

 上記を追加するにはどう記載すれば良いでしょうか？

覚えたての初心者なので、見よう見まねで作っています。
不自然なところがあればご指摘もいただきたいです。
よろしくお願いします。

 「行なっていること」(作動はしています)
報告書(氏名).xlsm（コピー元）
 集計表.xlsm（貼り付け先）

 ・報告書(氏名)ファイル内のマクロ実行で、
   報告書(氏名)のL列orM列orN列に入力されたものをコピーし、
   集計表に貼り付けます。

 例
 報告書(田中).xlsm
18行    L列　M列　N列
19行　　50   20
　　　　10      
　　　　35   10

 ・19行目以降にひとつでも値があれば、
　その列の19行以降をコピーします。
　なければ左の列を見に行きます。

 ・ 集計表の10行目に氏名が入力されていますので、
　　その氏名の下の行に貼り付けます。

 田中 佐藤 山田
 20

 ・貼り付け後、貼り付け先は閉じる
－－－－－－－－－－－－－－－－－－－－－－－－
 Sub test() 
 Dim s As String, sn As String
 Dim Rng As Range
 Dim cp(2) As Range 
 Dim i As Long, LastRow As Long

 Const fld As String = "\\〇〇\集計表.xlsm" 
 s = Split(ActiveWorkbook.Name, "(")(1) 
 sn = Split(s, ")")(0) 
 LastRow = Cells(Rows.Count, 1).End(xlUp).Row

 Set cp(2) = Range("L19:L" & LastRow) 
 Set cp(1) = Range("M19:M" & LastRow) 
 Set cp(0) = Range("N19:N" & LastRow)

 For i = 0 To 2 
 If WorksheetFunction.CountBlank(cp(i)) <> LastRow -18 Then 
 cp(i).Copy 
 Exit For 
 End If 
 Next

 Workbooks.Open fld

 If ActiveWorkbook.ReadOnly = True Then 
 MsgBox "このブックは、他者が利用中です。" 
 ActiveWorkbook.Close False 
 Exit Sub 
 End If

 Set Rng = Range("10:10").Find(what:=sn, lookat:=xlWhole) 
 If Not Rng Is Nothing Then 
 Sheets("Sheet1").Cells(11, Rng.Column).Select
 Selection.PasteSpecial Paste:=xlPasteValues
 Application.CutCopyMode = False 
 ActiveWorkbook.Save 
 ActiveWorkbook.Close True 
 MsgBox "貼り付け終了しました。" 
 Else 
 MsgBox "一致する氏名がないため貼り付けできませんでした。" 
 Application.CutCopyMode = False 
 ActiveWorkbook.Close True

 End 
 End If 
 End Sub

＜使用 Excel：Excel2003、使用 OS：Windows10 ＞

 ＞ ・貼り付け先のブックを既に開いている場合はそのまま使用し貼り付け、
 私はファイルサーバのファイルをWorkbooks.Openで開いた経験が無いので根拠がありませんが、
 Workbooks.Openをフルパス指定で記述した場合は、既にブックが開いていても問題なくコードは進むと思います。
（読み取り専用云々は別として）

 ＞貼り付け後もクローズしない。
 単純に ActiveWorkbook.Close しなければ良いのでは。

 ＞不自然なところがあればご指摘もいただきたいです。
  基本的にActiveWorkbookやActiveSheetに頼った記述は意図せぬ挙動が起こり易いのでオススメされていません。
 VBAではオブジェクトを明示すれば、ブックやシートを都度アクティブにする必要はありません。
 上手に説明出来るほどスキルがありませんので一例だけ。
 オブジェクト変数（ここではWorkbook）を用いる方法です。

 Sub sample()
    Dim wb As Workbook
    Set wb = Workbooks.Open("\\〇〇\集計表.xlsm")
    ThisWorkbook.Activate   '説明の都合上、アクティブにしています
    Debug.Print wb.Worksheets(1).Name
    Debug.Print wb.Worksheets(1).UsedRange.Cells(1, 1).Value
 End Sub

 上記のコードをステップ実行すると、集計表.xlsmがアクティブになっていなくても、
 そのブックの内容がイミディエイトウインドウに表示されていると思います。
 これを踏まえて、コピー元・コピー先のブックやシートを明確にして記述されると、良いのではないでしょうか。 
（MixNuts） 2021/12/03(金) 21:52

ありがとうございます。
自分で貼り付け先のブックを開いたまま実行すると、(読み取りではないです)
1004 RangeクラスのPasteSpecialメソッドが失敗しました
となってしまうのです。
閉じた状態であれば問題なく動くのですが…

開いたまま、閉じた状態から、どちらでも対応できるようにしたいのです。

（もち） 2021/12/03(金) 22:20

 もちさんのコードはActiveWorkbook主体で書かれていますよね。
 コードが書かれているブックでマクロ実行すると、
 その時点でのActiveWorkbook＝コードが書かれているブックですが、
 閉じている状態→Workbooks.Open fld でActiveWorkbook が集計表.xlsmに移る
 開いている状態→Workbooks.Open fld ではActiveWorkbookが集計表.xlsmに移っていない
 という事なんじゃないでしょうか。
 ※ただ私の環境（ローカルドライブ）でコードを実行すると、Workbooks.Openを経過すると、
 　ブックがアクティブになっているので自信はありませんが。
 　ともかく、先述のオブジェクトを明示すれば、その様な事態は避けられると思います。

 既存のコードのまま行くなら、該当部分を下記でどうでしょうか。

 Workbooks("集計表.xlsm").Sheets("Sheet1").Cells(11, Rng.Column).PasteSpecial Paste:=xlPasteValues
（MixNuts） 2021/12/03(金) 22:41

　なるほど！！そういうことかもしれません、、
　試してみます。
　仕事中でないとダメなのですぐに結果報告は出来ませんが…
　わかりやすく説明いただきありがとうございます！
（もち） 2021/12/03(金) 23:04

うまく行きました！
教えていただきありがとうございました！
（もち） 2021/12/06(月) 13:44

コメント返信：

[ 一覧(最新更新順) ]

[[20211203194225]] 『マクロで値をコピーして別のブックに貼り付け』（もち） ページの最後に飛ぶ

[[20211203194225]]　『マクロで値をコピーして別のブックに貼り付け』（もち）　ページの最後に飛ぶ