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

このQ&Aのポイント
  • エクセルの特定のシートにある関数がコピーされた際に、値だけをコピーするマクロが失敗する問題が発生しています。
  • 現在理解できていない関数が使用されているため、この問題が発生している可能性があります。
  • 関数を含むシートでも値のみをコピーするマクロを実行する方法を教えていただきたいです。
回答を見る
  • ベストアンサー

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

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

  • 5goma
  • お礼率82% (265/321)

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

  • ベストアンサー
  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.2

あれあれあれ。ちょっと間違いました。ごめんなさい。 訂正: 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 >この、「ブック」とはマクロが利かないシートがあるブックのことでしょうか。 いいえ、ちがいます。 再掲: >シートコピーした時点でこのお約束が破られているため、失敗します。 マクロでシート複写して作成された新しいブックは、保存されていません。 #ていうか。 「失敗」って具体的に何がどう失敗しているのかご相談に書かれていないので、あてずっぽで回答しています。

5goma
質問者

お礼

keithin 様 ありがとうございました。お蔭様で解決しました。 説明不足にもかかわらず御推察いただき申し訳ありませんでした。 マクロで作ったブックが保存されていないので関数が利かないケースがあるというのも勉強になりました。 大切に使わせていただきます。 簡単で恐縮ですが、お礼申し上げます。

その他の回答 (1)

  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.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

5goma
質問者

お礼

keithin 様 ありがとうございました。 教えていただいたマクロを使わせていただいたところ A1セルは成功しましたが、B3セルの値がA1セルと同じになりました。 関係ないかもしれませんが、マクロが利かないシートについて補足します。 B3セルの関数はE列までフィルハンドルでコピーしてあります。 また、B3からE3の行を10行目まで同じくコピーしてあります。 そのせいでしょうか、B3セルをコピーしたセルすべてがA1セルと同じ値になっています。 >A1セルの数式について、キチンと計算させるために「ブックが保存されている必要がある」 この、「ブック」とはマクロが利かないシートがあるブックのことでしょうか。 そうでしたら、保存済みになっています。 言葉足らずで申し訳ありませんが、さらに教えていただけないでしょうか。 よろしくお願いします。

関連するQ&A

  • エクセルのブック間で値のみコピー

    エクセル 2003使用です。 エクセルのブック間におけるシートのコピーについてお願いします。 Book.Aのシート1の各セルは、関数の数式で入力(出力)されています。 このシートをコピーして、Book.Bにペーストしたいのですが、 コピーは、関数計算後の”結果の文字列”で行いたいです。 Book.Aのシート全体をコピーして Book.Bのシートに「形式を選択して貼り付け」 → 「値」 で可能なのですが、シート数が多いので困っています。 Book.Aのシートタブを右クリックして「移動またはコピー」で シートのコピーをすると、数式でコピーされてしまいます。 この方法で、値のみコピーのような方法はありませんでしょうか? よろしくお願いします。

  • マクロ(エクセルシートコピー先)に関して

    分かりましたら教えてください。 A.xlsのシートBを、新しいブックにコピーし移動するときに、そのブック名が、常にそのシートBのセルC3に入力されている文字の名前にするようにマクロを組みました。(....Filename:Range("C3")としています。) さらにC.xlsのシートDもその新しいブックにコピー移動させるマクロを追加したいのですが、コピー先をどのように指定すればいいのでしょうか。。

  • シートコピーと値の転記 シート名変更マクロを

    エクセル2010で マクロのご教授をお願いいたします。 BOOK1の Sheet1 A1~A10に 数値1000が入力されているとして Sheet1のシートのコピーを作成 Sheet1にもどり A1~A3の値だけコピーして 作業列C1に 値を貼り付け A1~A10 をクリアーにしたのちに  ※↓が うまくいきません。 A1の値→ A8 A2の値→ A9 A3の値→ A10 に入力 させるマクロは どうなるでしょうか? マクロの記録で いろいろと やってみましたが どうも ※の部分が うまくいきません どう考えてもエクセルに対しての 理解不足だと思われますが、ご教授のほどお願いします。 おこなった自動マクロの記録は↓です。 マクロの記録開始する 名前 シートコピー Sheet1を右クリックして移動またコピー コピーを作成にチェックをいれ OK コピーシートが作成される。 Sheet1をクリックしなおして A1からA3をコピー  作業列として C1に 形式を選択して貼り付け 値 A1~A10を delete でクリア C1~C3を選択 コピー A8に 形式を選択して貼り付け 値 マクロの記録終了。 あと Sheet1をコピーするさいに シート名を前日の日付に自動的に変更できればと いいなと 考えておりますが できるのでしょうか? 例えば、本日でいえば シート名→ 0112 のような シート名を変更できればすてきだと おもいます。 どうぞ よろしくお願いします。

  • エクセルのマクロでシートのコピー

    いつもお世話になっております。 エクセル2000で次のことをマクロで行いたいのです。 いくつかWorkbookが開いている状態で、シートのコピーを行うのですが、シート名やブック名はその都度違います。 具体的には、Workbook"A" に "a"、"b"、"c" の3つのシート、Workbook"B" に "f"、"g"、"h"の3つのシートがあり、 Workbook"A" の Sheet("a") を Workbook"B" の Sheet("h") の前にコピーしたいのです。 Sheets(1).Copy before:=Workbooks("B.xls").Sheets(3) ところがWorkbookの名前がその都度変わるので困っています。 ブック間の移動は ActiveWindow.ActivateNext などで行っていますがシートのコピーがどうしても分からないので お願いします。  

  • エクセル 何故かシート間の値のコピーが出来ない

    いつもお世話になります。 開いているブックのシート「リスト1~3」に、Book1.xlsの「リスト1~3」の値をコピーする為に、下記のマクロを作成しました。 Dim SH1, SH2, SH3, SH4, SH5, SH6 As Worksheet Set SH1 = ThisWorkbook.Worksheets("リスト1") Set SH2 = ThisWorkbook.Worksheets("リスト2") Set SH3 = ThisWorkbook.Worksheets("リスト3") Set SH4 = Workbooks("Book1.xls").Worksheets("リスト1") Set SH5 = Workbooks("Book1.xls").Worksheets("リスト2") Set SH6 = Workbooks("Book1.xls").Worksheets("リスト3") 'リスト1をコピーする D = SH4.Range("A1").CurrentRegion.Rows.Count E = SH4.Range("A1").CurrentRegion.Columns.Count SH1.Range(Cells(1, 1), Cells(D, E)).Value = SH4.Range("A1").CurrentRegion.Value 'リスト2をコピーする F = SH5.Range("A1").CurrentRegion.Rows.Count G = SH5.Range("A1").CurrentRegion.Columns.Count SH2.Range(Cells(1, 1), Cells(F, G)).Value = SH5.Range("A1").CurrentRegion.Value 'リスト3をコピーする H = SH6.Range("A1").CurrentRegion.Rows.Count I = SH6.Range("A1").CurrentRegion.Columns.Count SH3.Range(Cells(1, 1), Cells(H, I)).Value = SH6.Range("A1").CurrentRegion.Value 以上を実行すると、「アプリケーション定義またはオブジェクト定義のエラーです」とエラーメッセージが出てしまいます。 それぞれのシートの処理の時に、 SH1.Select SH2.Select SH3.Select を入れて、シートを選択してから実行すると問題なく動くのですが、何故このようなことが起こるのでしょう?

  • 複数のセルをコピーし、別シートの任意のセルへペーストをするためのマクロ

    ブック(1)のシート(1)にある複数の離れたセル(A1,B2,C3,D4:F4)をコピーし、別ブックのシート(1)のセル(E1,F2,G3,B4:F4)へリンク貼り付けを実行するためのマクロを教えてください。 なお、ブック(1)とブック(2)は同一階層に置いてあります。 このブック(1)とブック(2)の表のフォーマットが違っており、ブック(1)のセルA1をコピーして ブック(2)のセルE1へ貼り付けし、 また同様にブック(1)のセルB2をコピーして ブック(2)のセルF2へ貼り付けをしていきます。 (A1→E1、B2→F2、C3→G3、D4:F4→B4:F4 へ貼り付ける) この作業をマクロを使って自動実行させたいと思っています。 御手数お掛け致しますが、何卒よろしくお願いいたします。

  • ExcelVBA シートコピーについて

    何度か試行錯誤したのですが、何故かうまくいかないので質問させていただきます。 エクセルで 「A」という名のBook内にSheet1にある値を参照して該当する値を返すSheet2があります。(ここまではVlookup関数などで処理できます。) このSheet2を全く新しいBookにコピーしてブック名を「B」、シート名をSheet2のB1の値にします。 このシートBにBook「A」のSheet2のA1の値を変化させたもの(添付画像のの場合だとだと1~3)をBook「B」のSheetの末に1つずつコピーしたいと考えています。 最終形はBook「B」に「山田」~「高梁」までのデータがシートごとに集約されたものを作りたいのですが、Sheetの貼り付けををする際にエラーが出てうまくいきません。 つたない説明で恐縮ですが、どなたかお知恵を拝借できないでしょうか。

  • シートコピーでフォントが変わる

    Excel2016を使用しています。 マクロにてシートコピーする処理を行っているのですが、コピー後のフォントがオリジナルと変わってしまう事象に悩んでおります。 ブックAからブックBへ、ブックAのシートAとシートBをworkbooks.sheets.copyを利用してコピーしています。 シートAは問題なくコピーされます。 シートBは内容自体は問題ないのですが、一部分だけフォントが変わってしまいます。 基本的にはMS Pゴシックだったものが、游ゴシックに変わってしまいます(添付画像を見てもらえればと思います)。 ※部分はシートAとシートBで違いがあった場合のチェック用に関数が入っており、シートコピー後に値の貼り付けをして文字列に変えています。 どうして一部のフォントが変わってしまうのでしょうか? ご存知の方はいらっしゃいますでしょうか? ちなみにExcelの新規作成時のフォントは游ゴシックです。

  • EXCELでワークシートを検索して値を返したい。

    こんばんは。 いろいろ調べてみたのですが、わからなかったので質問させていただきます。 EXCELで「111」「222」「333」「444」「555」という五つのワークシート名を持つブックがあります。 「111」のシートのセル"A1"に,"222"と入力すると、隣のセル"A2"にシート「222」の特定のセルの値(例えばC20のセルの値)を返し、セル"A1"に「555」と入力するとセル"A2"にシート「555」の特定のセルの値を返すといったものを作るには、どうしたらいいんでしょうか? できれば関数だけで作りたいのですが、もしむりならマクロではどういうコードになるのでしょうか? よろしくお願いします。

  • INDEX関数の値を合計する方法

    =INDEX(Sheet2!C5:C100,MATCH(Sheet3!B4,Sheet2!A5:A100,0)-1) という関数で出た値と、 =INDEX(Sheet2!C5:C100,MATCH(Sheet3!C4,Sheet2!A5:A100,0)-1) という関数で出た値の合計を、Sheet1のセルに入れたいのですが、どのようにすればスマートにいくでしょうか。(検索値である、Sheet3!B4,の部分が違います) =INDEX(Sheet2!C5:C100,MATCH(Sheet3!B4,Sheet2!A5:A100,0)-1)+=INDEX(Sheet2!C5:C100,MATCH(Sheet3!C4,Sheet2!A5:A100,0)-1) とすれば、合計は出るのですが、 Sheet3のB4、C4、D4・・・と、4の行を検索した結果の値を足していきたいのです。 そうなると、 =INDEX(Sheet2!C5:C100,MATCH(Sheet3!B4,Sheet2!A5:A100,0)-1)+=INDEX(Sheet2!C5:C100,MATCH(Sheet3!C4,Sheet2!A5:A100,0)-1)+・・・・・・・・ と、非常に長くなってしまいます。 他の関数でスマートに計算する方法はないでしょうか。

専門家に質問してみよう