こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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

投稿日時 - 2012-10-31 00:29:48

QNo.7774474

困ってます

質問者が選んだベストアンサー

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


訂正:
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



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

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

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




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

投稿日時 - 2012-10-31 08:41:33

お礼

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

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

投稿日時 - 2012-10-31 09:01:26

ANo.2

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.1

こんばんは。

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


作成例:
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

投稿日時 - 2012-10-31 00:51:14

お礼

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

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

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

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

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

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

投稿日時 - 2012-10-31 07:48:18

あなたにオススメの質問