Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2013)
アクティブシートではないシートの操作(valueプロパティ)
投稿日時: 18/04/20 11:45:40
投稿者: maruco33

こんにちは、初心者です。
いろいろ調べたのですが、解決策が見つからずこちらへ助けを求めた次第です。
やりたい作業は、別シートへ設置したコマンドボタンを押すと、
”内訳”シートの表をコピーして、値のみそのまま貼り付けをしたい。という事です。
以下のマクロを組んでみました。
 
Sub test ()
Dim Mrow As Variant
 
With Worksheets("内訳")
 
'書式設定があるセルの最終行数を取得
    Mrow = .UsedRange.Rows.Count
 
'範囲はA6:最終行と8列(H)まで
.Range(Cells(6, 1), Cells(Mrow, 8)).Value = .Range(Cells(6, 1), Cells(Mrow, 8)).Value
 
End With
End Sub
 
”内訳”シートをアクティブにして実行してみると、うまくいくのですが
別シートをアクティブにして実行すると
 
”アプリケーション定義またはオブジェクト定義のエラーです”と出ます。
一応、以下に差し替えてみて実行してみるとうまくいきました
'.Range("a6:h119").Value = .Range("a6:h119").Value
 
言葉の意味を調べたりしましたが、皆目見当がつかず調べる術を失ってしまい
時間だけが経過するだけで、助けを求めることにしました。よろしくご教授ください。

回答
投稿日時: 18/04/20 12:50:32
投稿者: mattuwan44

>.Range(Cells(6, 1), Cells(Mrow, 8)).Value = .Range(Cells(6, 1), Cells(Mrow, 8)).Value
 
.Range(.Cells(6, 1), .Cells(Mrow, 8)).Value = .Range(.Cells(6, 1), .Cells(Mrow, 8)).Value
 
 
Worksheets("内訳")Cells(6, 1)
Worksheets("内訳")Cells(Mrow, 8)
 
「の」がピリオドに対応しています。
つまり
Worksheets("内訳")の
と書かずに
Cells(6, 1)
とシートの修飾を省略すると、
アクティブシートのCells(6, 1)
という風に勝手に解釈されます。
で、
>.Range(Cells(6, 1), Cells(Mrow, 8))
は、
 
Worksheets("内訳").Range(activesheet.Cells(6, 1), activesheet.Cells(Mrow, 8))
という意味に解釈され、アクティブシートがWorksheets("内訳")でない場合は、
どっちのシートなのか整合性がないため、
「オブジェクト定義のエラーです(=セル範囲の指定が間違っている)」
ということになります。
 
>一応、以下に差し替えてみて実行してみるとうまくいきました
>'.Range("a6:h119").Value = .Range("a6:h119").Value

括弧の中の文字列にシートの指定がないので(逆に指定も出来ますが。)、
外側の.Rangeに掛かっているシートであるとエクセル君が解釈します。
 
で、翻って、
>”内訳”シートの表をコピーして、値のみそのまま貼り付けをしたい。
どのシートに貼りつけたいですか?
それがコードの中に表現されてないので、
どうやっていいか、僕も実行時エラー?で、その作業を完了できないので、
エクセル君も困るでしょう。

回答
投稿日時: 18/04/20 13:26:46
投稿者: よろずや

.Range(Cells(6, 1), Cells(Mrow, 8)).Value = .Range(Cells(6, 1), Cells(Mrow, 8)).Value
                    ↓
Range(Cells(6, 1), Cells(Mrow, 8)).Value = .Range(.Cells(6, 1), .Cells(Mrow, 8)).Value
ですね。

投稿日時: 18/04/20 13:29:32
投稿者: maruco33

mattuwan44 様
>.Range(.Cells(6, 1), .Cells(Mrow, 8)).Value = .Range(.Cells(6, 1), .Cells(Mrow, 8)).Value
で、解決できました。
>Worksheets("内訳")のCells(6, 1)
>Worksheets("内訳")のCells(Mrow, 8)
なるほど!Cellsに対して、シートの指定の仕方と.Rangeに対してのシートの指定に違いがあるのなんて
全く理解していなかったです。勉強不足でした。
ありがとうございます。
 
>どのシートに貼りつけたいですか?
 
mattuwan44 様が言う通り言葉足らずで申し訳ありません。正しくはコピーしたシートの同シート、同範囲の
セルに値のみ貼り付けという事です。
シートには数式が入っているので、それをエクセルデータとして他社に渡すために、どうしても
計算後の値をそのままに、数式を削除する必要があったのです。
いろいろ模索していただき感謝いたします。
今後とも精進いたします。