Excel (VBA)

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

 
(Windows 10 Pro : Excel 2019)
貼り付けについて
投稿日時: 22/08/05 02:52:28
投稿者: 暁の翼

  Cells.Select
    Selection.Copy
     
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues
 
上のようにシートの全セルを値に変換したいのですが、なぜかうまくいきません。
コピーモードにはなっているので、どうやら貼り付けの方がうまくいっていないようです。
特にエラーなどが出るわけでもなく、また出来る時と出来ない時があります。
わかる方いましたらご教示ください。

回答
投稿日時: 22/08/05 05:25:00
投稿者: WinArrow
投稿者のウェブサイトに移動

>Cells.Select
セル全部という指定を
データの入っているセルというように変更してみましょう。
 
複写元シートと複写先シートが同じでしたら
 
With ActiveSheet.Range("A1").CurrentRegion
     .Value = .Value
End With
に変更してみましょう。

回答
投稿日時: 22/08/05 09:34:02
投稿者: simple

ご指摘のとおりと思います。
私も、コード全般において、極力Selectはしないようにしたほうがよいと思います。
 
なお、UsedRangeを使ったほうがよいかもしれませんね。
すべてのデータがCurrentRegionでカバーできる保証はないでしょうから。

    With ActiveSheet.UsedRange
        .Value = .Value
    End With
もちろん、ActiveSheetは例示であって、
なんらかのWorksheetオブジェクトであればOKです。

投稿日時: 22/08/05 10:38:58
投稿者: 暁の翼

回答ありがとうございます。
お二方とも試してみたのですがどうも思ったような挙動になりません。
 
WinArrow様の方は全く値に置き換わらず
simple様の方は書式がリセットされてしまうのですが、値のみの貼り付けはできないでしょうか?
 
引き続きご教示いただけると幸いです。
よろしくお願いいたします。
 
 
 

回答
投稿日時: 22/08/05 10:54:32
投稿者: simple

質問に直接は答えていませんでした。追記しておきます。
 
(1)
再現性が無いので、原因解明はむずかしいですね。
(2)
一つ考えられるのは、そのコードが標準モジュールに書かれていると、
Activesheetを対象としたものになりますが、
マクロを実行したときに、目指すシートとは別のシートがアクティブになっていた可能性があります。
可能性ということにとどまりますが。
シートを名称かなにかで明示的に指定する(Worksheets("〇〇")といった書き方)、
というのが差し当たっての改善策でしょう。
 
(3)
上記以外で、仮にそうしたこと(値コピーがなされない)起きていても、
Excel内部の問題なので、一般ユーザーには理由はわからないと思います。
 
(4)
なお、
・UsedRangeを使ってCopy範囲を限定しても、速度的な改善は大したことはなかったです。
  未使用領域のコピーが足を引っ張ることは無いようです。
・.Value = .Value方式のほうはCopy方式に比べて時間はかかります。
・また、Cellsに対して.Value = .Value方式は適用不可です。メモリ不足になるリスク大です。

回答
投稿日時: 22/08/05 11:00:18
投稿者: simple

ああ、コメントがあったのですね。拝見せずに投稿しています。
 
>simple様の方は書式がリセットされてしまうのですが、値のみの貼り付けはできないでしょうか?
ちょっと思い当たることがないですね。値だけを操作しているはずです。
どんな書式なんですか?
 
前のコメントで書きましたが、
Worksheets("Sheet1")といったようにシート名を指定したコードにしたみてください。
そして、実際に実行したコードを提示してもらえますか?

Sub aaa
    
End Sub
のように全体を示してください。

投稿日時: 22/08/05 11:28:20
投稿者: 暁の翼

simple様
引き続きの回答ありがとうございます。
こちらの勘違いで書式ではなくシート内のテーブルでした。
コピー自体は問題なく行われました。
テーブルの保持は厳しいですかね?

投稿日時: 22/08/05 11:36:58
投稿者: 暁の翼

一応コードもあげておきます。
 
Option Explicit
 
Sub 月次週休表確定()
 
'週休表 印刷用の定義
 
Dim syukyu As Worksheet
 
If Month(Now) Mod 2 = 0 Then
 
    Set syukyu = ThisWorkbook.Worksheets(4)
Else:
    Set syukyu = ThisWorkbook.Worksheets(3)
 
End If
 
Dim tuki As Variant
Dim kensaku As String
Dim uwagaki As Integer
Dim result As Integer
 
tuki = Format(syukyu.Cells(3, 3), "m月")
 
result = MsgBox(tuki & "の休みを確定します" & vbCrLf & "よろしいですか?", vbYesNo)
     
    If result = vbNo Then
        Exit Sub
    End If
     
On Error Resume Next
 
kensaku = Sheets(tuki & "週休表").name
 
If Err.Number = 0 Then
    uwagaki = MsgBox("上書きしてよろしいですか?", vbYesNo)
         
        If uwagaki = vbNo Then
        MsgBox "中断しました"
        Exit Sub
         
    Else: Application.DisplayAlerts = False
 
           Sheets(tuki & "週休表").Delete
 
            Application.DisplayAlerts = True
     
        End If
         
End If
 
 
 
syukyu.Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.name = tuki & "週休表"
 
'ここで非表示セルを全再表示
 
 With Worksheets(tuki & "週休表").UsedRange
        .Value = .Value
    End With
     
 
 
ThisWorkbook.Worksheets(2).Activate
 
MsgBox "完了しました"
 
 
End Sub

回答
投稿日時: 22/08/05 13:24:00
投稿者: hatena
投稿者のウェブサイトに移動

Value の代入だとテーブルは解除されるみたいですね。
 

    With Worksheets(tuki & "週休表").UsedRange
        .Copy
        .PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End With

回答
投稿日時: 22/08/05 13:45:47
投稿者: simple

今、お二方のコメントを拝見しました。
テーブルでしたか。
テーブルは解除されるとは、知りませんでした。ありがとうございます。
 
hatenaさんのご提案は、確かにテーブルは残り、値になることを確認しました。
これは、シートの特定や、UsedRangeの利用はありますが、
基本的には現行処理と同じということですね。
 
質問者さんにお尋ねしますが、
Cellsでも同じことでしょうから、処理がされなかった理由は、
要するに、対象のシートの指定が甘かったという理解でいいんでしょうか?
 
残された課題は何かあるのでしょうか?

投稿日時: 22/08/06 06:50:00
投稿者: 暁の翼

 hatena様、ご回答ありがとうございます。
仕事の都合で返信遅くなり申し訳ありません。
 
試してみましたが、やはり当方の環境では値貼り付けがされませんでした。
どうもPCの環境によるもののような気がします。
今回のこの件は1つづつ各セルに値を代入することで代替することにしました。
 
何かすっきりはしませんが、これで締めさせていただきます。
ご教示いただいた3名の方々ありがとうございました。