エクセル 複数シートの値のみコピーで

質問

こんにちは　いつもお世話になっています。

先日、選択した複数のシートを新しいブックに値だけコピーするマクロをこちらで教えていただきました。（関数が入ったシートなのでタブの右クリックからの新規ブックへのコピーでは関数がコピーされてしまうので）
今回、このマクロで失敗するシートがあったので原因を教えてください。
値だけコピーするマクロは以下です。

Sub 値コピー()
Dim WS As Worksheet

ActiveWindow.SelectedSheets.Copy

For Each WS In ActiveWorkbook.Worksheets
With WS.Cells
.Copy

.PasteSpecial Paste:=xlPasteValues
End With
Next

Application.CutCopyMode = False
End Sub

失敗するシートにはつぎの関数があります。

A1セルに=REPLACE(CELL("filename",A1),1,FIND(".xlsx]",CELL("filename",A1))+LEN(".xlsx]")-1,)

B3セルに=IF(COUNTIF(Sheet2!$1:$1,$A$1),IF(ROWS($3:3)>COUNTIF(OFFSET(Sheet1!$J:$J,,MATCH($A$1&"クラス",Sheet1!$J$1:$N$1,0)-1),B$2),"",COUNTIFS(OFFSET(Sheet1!$C:$C,,MATCH($A$1,Sheet1!$C$1:$G$1,0)-1),">"&INDEX(Sheet1!$C:$G,MATCH(B$2&"☆"&ROWS($3:3),OFFSET(Sheet2!$A:$A,,MATCH($A$1,Sheet2!$A$1:$E$1,0)-1),0),MATCH($A$1,Sheet1!$C$1:$G$1,0)),OFFSET(Sheet1!$J:$J,,MATCH($A$1&"クラス",Sheet1!$J$1:$N$1,0)-1),B$2)+1&"位 "&INDEX(Sheet1!$B:$B,MATCH(B$2&"☆"&ROWS($3:3),OFFSET(Sheet2!$A:$A,,MATCH($A$1,Sheet2!$A$1:$E$1,0)-1),0))&" "&INDEX(Sheet1!$C:$G,MATCH(B$2&"☆"&ROWS($3:3),OFFSET(Sheet2!$A:$A,,MATCH($A$1,Sheet2!$A$1:$E$1,0)-1),0),MATCH($A$1,Sheet1!$C$1:$G$1,0))&"点"),"")

別シートのデータから条件に合うものを引き出す関数です。
これらの関数もおしえていただいたもので、まだ理解できていませんので、説明不十分で申し訳ありません。
これらの関数があるシートではなぜ上記のマクロが失敗するのでしょうか。
もちろん、シートのデータを選択、コピーして新規ブックに値のみコピーはできます。
上記のような関数があるシートでも複数選択シートでの値のみコピーができるマクロを教えていただけないでしょうか。

情報不足がありましたら、教えてください。
よろしくお願いします。
エクセル2007

回答No.2

keithin · ベストアンサー

あれあれあれ。ちょっと間違いました。ごめんなさい。

訂正：
sub macro1()
　dim w as worksheet
　application.calculation = xlcalculationmanual
　activewindow.selectedsheets.copy
　on error resume next
　for each w in worksheets
　　with w.usedrange
　　　.value = .value
　　end with
　next
　application.calculation = xlcalculationautomatic
end sub

＞この、「ブック」とはマクロが利かないシートがあるブックのことでしょうか。

いいえ、ちがいます。
再掲：
＞シートコピーした時点でこのお約束が破られているため、失敗します。

マクロでシート複写して作成された新しいブックは、保存されていません。

＃ていうか。
「失敗」って具体的に何がどう失敗しているのかご相談に書かれていないので、あてずっぽで回答しています。

お礼

keithin 様　ありがとうございました。お蔭様で解決しました。
説明不足にもかかわらず御推察いただき申し訳ありませんでした。

マクロで作ったブックが保存されていないので関数が利かないケースがあるというのも勉強になりました。
大切に使わせていただきます。
簡単で恐縮ですが、お礼申し上げます。

回答No.1

keithin

こんばんは。

Ａ１セルの数式について、キチンと計算させるために「ブックが保存されている必要がある」という説明がありませんでしたか。
シートコピーした時点でこのお約束が破られているため、失敗します。

作成例：
sub macro1()
dim w as worksheet
　application.calculation = xlcalculationmanual
　activewindow.selectedsheets.copy
　on error resume next
　for each w in worksheets
　　with w.cells.specialcells(xlcelltypeformulas)
　　　.value = .value
　　end with
　next
　application.calculation = xlcalculationautomatic
end sub

お礼

keithin 様　ありがとうございました。

教えていただいたマクロを使わせていただいたところ
A1セルは成功しましたが、B3セルの値がA1セルと同じになりました。

関係ないかもしれませんが、マクロが利かないシートについて補足します。
B3セルの関数はE列までフィルハンドルでコピーしてあります。
また、B3からE3の行を10行目まで同じくコピーしてあります。
そのせいでしょうか、B3セルをコピーしたセルすべてがA1セルと同じ値になっています。

＞Ａ１セルの数式について、キチンと計算させるために「ブックが保存されている必要がある」

この、「ブック」とはマクロが利かないシートがあるブックのことでしょうか。
そうでしたら、保存済みになっています。

言葉足らずで申し訳ありませんが、さらに教えていただけないでしょうか。
よろしくお願いします。