VBA PasteSpecialメソッドの値貼り付けをするとエラーが発生する

Question

前提・実現したいこと

Accessで作成したデータを、計算式が入力されている既存のExcelに貼り付け
再計算後、全セルを値貼り付けしたい（計算式をなくしてファイルを軽くするため）

行数は1500前後、列数が850列あります。
計算式が入っている列を750列位に減らすとエラーなく実行できます。
列数に制限などありますでしょうか？

.Cells.PasteSpecial xlPasteValues 中に以下のエラーメッセージが発生しました。

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

RangeクラスのPasteSpecialメソッドが失敗しました。

該当のソースコード

With xlBook.Worksheets("Sheet1")
.Cells.Copy
.Cells.PasteSpecial xlPasteValues ← ここでエラー
End with

試したこと

セルを範囲指定してクリップボードを使わず貼り付けしたり、
配列を使用した貼り付けというパターンを試しましたが
どちらも下記のエラーメッセージが表示されました。
また、列数が多すぎるかと思い、100列ずつに区切りloopさせてみましたが、後半（750列以降)になると同じエラーが発生しました。
列の右側から（逆から）処理をしてもやはりエラーになりましたので、Excelの計算式が問題ということではなさそうです。

エラーメッセージ：アプリケーション定義またはオブジェクト定義のエラーです。

Maxrow = .Cells(.Rows.Count, 8).End(xlUp).Row
Maxcol = .Cells(8, .Columns.Count).End(xlToLeft).Column

'セルの範囲指定で貼り付けバージョン
.Range(.Cells(1, 1), .Cells(Maxrow, Maxcol)).Value = .Range(.Cells(1, 1), .Cells(Maxrow, Maxcol)).Value ←エラー

'配列を使用して貼り付けバージョン
Dim MyArray As Variant
MyArray = .Range(.Cells(1, 1), .Cells(Maxrow, Maxcol))
.Range(.Cells(1, 1), .Cells(Maxrow, Maxcol)) = MyArray ←エラー

何かお気づきのことがありましたらご教授よろしくお願いいたします。

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

windows7 64bit CPU intel i7 メモリ16GB
Office2010

Answer 1

kai_keitaiさん
hatena19さん

ご丁寧にご回答ありがとうございます。
教えていただいたことを色々なパターン（DoEventsを入れたり、自動計算を入れたり、配列にしてみたりUsedRangeにしてみたり）で試してみましたがエラーメッセージの内容は変わりませんでした。

今回の処理は、アクセス側でデータ加工し、既存Excelを開き "データ"シートへRecordsetを貼り付け
"Sheet1"で "データ"シートを参照した計算式を再計算後に値貼り付けをしておりました。

試しにデータシートへデータを貼り付けた後、いったんExcelファイルを保存し
開きなおした後に ExcelのVBEに上記の値貼り付けのコードを作成し実行したところ
5分以上かかっても貼り付けが完了しませんでした。(中断しました･･･)
Sheet1のほとんどのセルが計算式になっており、850列あるうち350列ほどsumifなどの処理の重い計算式になっています。
再計算が遅いのが原因ならと思い、計算式はまったくあってないのを承知の上で、
すべての計算式を単純にデータシートを参照するだけの式（=データ!A1 のような式）に変更して
実行したところ何なく実行できました。

以上のことから、Excelの計算式が重すぎるという結論になりました。
まずはExcelのsheet1の計算方法を見直すことを行っていきたいと思います。

多くのご助言大変ありがとうございました。

････Sheet1の計算式の見直しとデータ加工の見直し。850列もあるものを見直すのは心が折れそうです(T.T)

Comments

[sazi]

大変そうですね。
１．Access側に計算させる。
２．power queryを使用する。
３．スピル関数を使用する。
４．sumproduct()への置き換え。
みたいな感じで検討されてはどうでしょうか。

個人的には、
動作しなくなった際に変更を加えた箇所から、sumproduct()に式を置き換えるという事を行ってみますけどね。

Answer 2

下記だとどうなりますか。

With xlBook.Worksheets("Sheet1")
   .UsedRange.Value = .UsedRange.Value 
End with

あるいは、

Dim ary() As Variant
With xlBook.Worksheets("Sheet1")
   ary = .UsedRange.Value
  .UsedRange.Value = ary
End with

セル範囲が大きすぎるの原因だとしたら、下記のように1行ずつ上書きするとか。

Dim ary() As Variant
With xlBook.Worksheets("Sheet1")
   ary = .UsedRange.Value
  .UsedRange.Value = ary
End with

あとは、処理の前後に下記のコードを挿入して描画と計算を停止させるとか。

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'処理

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Answer 3

Paste:= が抜けてます。

PasteSpecial Paste:=xlPasteValues

Comments

[takamak]

ご回答ありがとうございます。
早速試してみましたが、残念ながら
「RangeクラスのPasteSpecialメソッドが失敗しました」のエラーが出ました。

[kai_keitai]

Sub Macro1()
Dim xlBook As Workbook
Set xlBook = ThisWorkbook
With xlBook.Worksheets("Sheet1")
Cells.Copy
Cells.PasteSpecial Paste:=xlPasteValues
End With
End Sub

単純ではありますが、実行しても全く問題ありません。

もしかして、ワークシートが重い（計算に時間がかかる等）で、全セルを値貼り付けしているのか。
と思い、下記のコードは？

Sub Macro1()
Application.Calculation = xlCalculationManual
DoEvents
Dim xlBook As Workbook
Set xlBook = ThisWorkbook
With xlBook.Worksheets("Sheet1")
Cells.Copy
Cells.PasteSpecial Paste:=xlPasteValues
End With
Application.Calculation = xlCalculationAutomatic
End Sub

自動計算の on と off を切替しながらは、どうですかね。
途中に、保存をするとか。

どうも、OSがらみの不安定要素もあるかもです。