Excel (VBA)

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

 
(Windows 10全般 : その他)
Cells.Copyについて
投稿日時: 24/03/05 11:26:32
投稿者: tako552101

基ブックには行列サイズを整形、セル結合したり、数式が入ったセルも混在している「帳票シート」があり、このシートを新規ブックに帳票レイアウトそのままで値のみの(基ファイルのリンクが残らない)シートを複製することが目的で、試行錯誤の結果、最終的に以下のコードとなりました。
 
これで問題ないのですが、分け合って現在ver2010でしか確認することができないので、それ以降のバージョンでも問題のないコードか教えていただけると助かります。
 

Sub TEST()
'コピー元のブック名指定
    Set wb = ThisWorkbook
'新規ブック
    Workbooks.Add
'ペースト先ブック名指定
    Set CopyMoto = ActiveWorkbook
'ブック間で値のみコピペ
    wb.Sheets("sheet1").Cells.Copy
    CopyMoto.Sheets(1).Paste
END SUB

この場合、狙いの通り値のみのコピペとなり、基ファイルのリンクも残らないので万事良かったのですが、コピーして普通にペーストすると数式も一緒にコピペされると思っていましたし「Cells.Copy」の情報も検索しきれませんでしたので、併せてご指導いただけるとありがたいです。

回答
投稿日時: 24/03/05 11:57:59
投稿者: Suzu

形式を選択して貼り付ける(PasteSpecialメソッド)
https://www.moug.net/tech/exvba/0050104.html
 
Paste:=xlPasteValues で良いのでは?

回答
投稿日時: 24/03/05 13:33:41
投稿者: WinArrow

>狙いの通り値のみのコピペとなり
 
本当ですか?

投稿日時: 24/03/05 14:22:13
投稿者: tako552101

みなさん、ありがとうございます。
 
>Paste:=xlPasteValues で良いのでは?
帳票のレイアウトも生かしつつコピペしたいので、xlPasteValuesではうまくいかず、さらに途中に結合セルなどあるとうまくいかなかったりするもので。
 
>>狙いの通り値のみのコピペとなり
>本当ですか?
 
それが事実なのでこれでいいのかな?と。基ファイルからコピペして新規ブックに張り付けるのに、はじめは「Sheets("sheet1").Copy」で複製していたんですが、それだと基ファイルの名前定義も一緒に複製されてしまって、リンクが残ったままのファイルになってしまうので試行錯誤しておりました。
 
やはり通常では数式などもコピーされるのが仕様なのでしょうか。2010以降で触れるPCが故障してしまったので検証できずに困っていました。

回答
投稿日時: 24/03/05 16:28:26
投稿者: 半平太

>>>狙いの通り値のみのコピペとなり
>>本当ですか?
>それが事実なのでこれでいいのかな?
 
事実ですかぁ・・
当方のXL2010は、値のみのコピーにはなりませんけども?
話の取っ掛りからして変なので、その先の議論をする気が起きないですねぇ。。

回答
投稿日時: 24/03/05 17:10:48
投稿者: WinArrow

試行錯誤しているうちに、複写元を「値」にしてしまった。
ということではないでしょうか?
 
よく確認した方がよいと思いますよ。

回答
投稿日時: 24/03/05 17:30:01
投稿者: Suzu

帳票のレイアウトも生かしつつコピペしたいので、xlPasteValuesではうまくいかず、さらに途中に結合セルなどあるとうまくいかなかったりするもので。
 
であれば、
 

Sub TEST()
  ThisWorkbook.Worksheets("Sheet1").Copy
  ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub

 
で済むかと。
 
Worksheet.Copy メソッド (Excel)
https://learn.microsoft.com/ja-jp/office/vba/api/excel.worksheet.copy
 
Before または After を指定しない場合、コピーした Worksheet オブジェクトを含む新しいブックが作成されます。 新しく作成されたブックには Application.ActiveWorkbook プロパティが保持され、1 つのワークシートが含まれています。 

 
WorkSheet に対し、引数を指定せずに Copyメソッドを実行し、Sheet1 を 新規ブックにコピー
 これで、書式も何もかもコピーされます。
 
コピーされた、新ブックの シートが、ActiveSheet となっていますので
 その 使用されている セル の Value を、自セルの値で上書き

回答
投稿日時: 24/03/05 17:46:59
投稿者: Suzu

引用:
はじめは「Sheets("sheet1").Copy」で複製していたんですが、それだと基ファイルの名前定義も一緒に複製されてしまって、リンクが残ったままのファイルになってしまうので試行錯誤しておりました。

 
あ。。見落としていました。
現状のコード の Paste の後に、Value = Value で良いでしょう。

回答
投稿日時: 24/03/05 18:30:24
投稿者: WinArrow

Suzu さんの引用:

現状のコード の Paste の後に、Value = Value で良いでしょう。

これを数式にょるリンクは消えていると
思いますので、
そのあと、名前定義を削除するとよいでしょう。

投稿日時: 24/03/06 01:07:15
投稿者: tako552101

みなさん、ありがとうございます。

引用:
>事実ですかぁ・・
>当方のXL2010は、値のみのコピーにはなりませんけども?
 
>試行錯誤しているうちに、複写元を「値」にしてしまった。
>ということではないでしょうか?

以下の方法をお試しただけないでしょうか。
新規ブックのsheet1に適当に文字を入力します。これはなんでもいいです。
A1→"あああ"、B1→"いいい"、C1→"ううう"、D1→"えええ"、E1→"おおお"、
A2→"かかか"、B2→"ききき"、C2→"くくく"、D2→"けけけ"、E2→"こここ"、など。
 
sheet2のA1に"=sheet1!A1"と数式を入れ、以下右、下にコピー。これで見た目はsheet1と同じになりました。書式もコピーさせたいので適当に列幅を変更したり罫線などで装飾しておきます。
sheet2のセル全体の書式設定→保護(ロック、表示しない)にしてシートを保護します。
 
以下のコードを実行。
Sub CellsCopyTest()

'コピー元のブック名指定
    Set wb = ThisWorkbook
'新規ブック
    Workbooks.Add
'ペースト先ブック名指定
    Set CopyMoto = ActiveWorkbook
'ブック間で値のみコピペ
    wb.Sheets("sheet2").Cells.Copy
    CopyMoto.Sheets(1).Paste
'シートトップへスクロール
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollRow = 1
    CopyMoto.Sheets(1).Range("A1").Select
'コピペモード解除
    Application.CutCopyMode = False

End Sub

装飾はそのまま、セル内はすべて値のみ(数式ではない)になっていると思います。テストでは名前定義まで行ってませんが、実際には名前定義の複製は行われず、リンクのないファイルができます。
 
最初の投稿では「sheet2のセル全体の書式設定→保護(ロック、表示しない)にしてシートを保護」の説明が抜けていましたが、これが数式を解除して値のみコピーする要因?(実際には「表示しない」&保護の場合に有効なようです)になっているとは思いませんでした。
 
シートを複製して値のみコピーする方法ですと、作成している帳票の途中に結合セルが多々あってシート全体を一度に「Value = Value」できませんし、名前定義のエラー解除も私のスキルでは難易度が高そうなので、このコードなら簡単に目的達成!と思ったのですが、使用上問題ありますでしょうか。
よろしくお願いします。

投稿日時: 24/03/06 01:17:17
投稿者: tako552101

Sub TEST()
  ThisWorkbook.Worksheets("Sheet1").Copy
  ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub

すみません、見落としました。
結合セルを気にしないで一気にコピペできました!ありがとうございます。

回答
投稿日時: 24/03/06 11:21:20
投稿者: WinArrow

tako552101 さんの引用:
Sub TEST()
  ThisWorkbook.Worksheets("Sheet1").Copy
  ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub

すみません、見落としました。
結合セルを気にしないで一気にコピペできました!ありがとうございます。

 
確認
Workbooks.Add はやめた?
複写元シートは、「Sheet2」では?

投稿日時: 24/03/06 13:12:29
投稿者: tako552101

引用:
確認
Workbooks.Add はやめた?
複写元シートは、「Sheet2」では?

簡単に実装できそうなので止めてませんが、使い方に誤りがあるのなら方向転換も必要かと。
結合セル混在でも1手でできるので、それはそれで勉強になったお礼です。

回答
投稿日時: 24/03/06 13:46:29
投稿者: Suzu

セルに名前定義があるのであれば
 

引用:
Sub TEST()
  Workbooks.Add
 
  ThisWorkbook.Worksheets("Sheet1").Cells.Copy
 
  With ActiveSheet
    .Paste
    .UsedRange.Value = .UsedRange.Value
    .Range("A1").Select
  End With
  Application.CutCopyMode = False
End Sub

 
の様に、
新たにワークブックを作成後、コピーをする事で 書式・数式はコピーするが 名前定義はコピーしない様になり
数式を 値として代入すれば良いかと。

回答
投稿日時: 24/03/06 16:08:21
投稿者: WinArrow

tako552101 さんの引用:
引用:
確認
Workbooks.Add はやめた?
複写元シートは、「Sheet2」では?

簡単に実装できそうなので止めてませんが、使い方に誤りがあるのなら方向転換も必要かと。
結合セル混在でも1手でできるので、それはそれで勉強になったお礼です。

 
実際にテストしたのでしょうか?
このコードでテストしたとしたら、「Sheet1」ですよね?
シートが保護している「Sheet2」ならば、エラーになるはず。
 
うまくいったで終了ではなく、
コード1行毎の意味を理解して・・・・応用ができる・・・で終了です。
 

投稿日時: 24/03/06 17:42:54
投稿者: tako552101

Suzuさん、ありがとうございます。
 
実際「.Paste」の時点で目的を達成(数式は解除され値のみペースト)しているのですが、それでも「.UsedRange.Value = .UsedRange.Value」は必要なのでしょうか。また、この使い方なら問題は起こらないでしょうか。
 
WinArrowさん、ありがとうございます。
 
ご指摘の通りです。申し訳ありません。
 
 
 

回答
投稿日時: 24/03/06 19:05:50
投稿者: Suzu

引用:
また、この使い方なら問題は起こらないでしょうか。

大概は 良いと思います。
 
質問の段階で、ロック・表示しない は 回答者には判らなかった事です。
ご提示された情報以外も回答者側にて推測して 動作を担保するのは難しいです。
 
また、今後の ExcelやVBAの仕様を変更もありえますから、未来については、誰にも判りません。
問題が発生した時点で対応するのが良いかと思います。
 
Pasteの時点で 目的達成とおっしゃっていますが、
ロック・表示しない・保護 が設定されており
標準ではない状態を前提としています。
 
「結果 目的が達成されているから良い」 という考え方と
「問題が起こらないか?」 と 他者に 担保を求めるのは 道理が合わないと感じます。

投稿日時: 24/03/06 19:34:23
投稿者: tako552101

みなさんありがとうございました。