Excel2003のVBAでプログラミングをしています。

質問

Excel2003のVBAでプログラミングをしています。
作成しているのは売上のデータを外部DBから抽出し既定のフォーマットに整形するツールです。
そのファイルを送信先各位の送信する際にそのWorkBookをそのまま送信すると
マクロが含まれている分動作が非常に重くなってしまいます。
そこで、同じフォーマットのコピーを作成したいのですが
workbookオブジェクトのSaveCopyasメソッドを使用してコピーを作成すると
マクロごとコピーされて意味がありません。
各worksheetの値のみをコピーする方法はありませんでしょうか。
どなたか助言おねがいします。

回答No.7

1050 円（@1050YEN） · ベストアンサー

形式を選択して貼り付ける(PasteSpecialメソッド)
http://www.moug.net/tech/exvba/0050104.htm

※xlPasteFormats
※xlPasteValues
の二回貼り付け

ただし結合セルがある場合は、結合されたセルの範囲だけでのペーストを行わなければならない

Sub 呼出例()
　　Dim l_xlsSht1　 As Worksheet
　　Dim l_xlsBok2　 As Workbook
　　Dim l_xlsSht2　 As Worksheet

　　'マクロの存在するシートの先頭を複写元
　　Set l_xlsSht1 = ThisWorkbook.Worksheets(1)

　　'新しいワークブックを追加して、その先頭を出力先
　　Set l_xlsBok2 = Workbooks.Add()
　　Set l_xlsSht2 = l_xlsBok2.Worksheets(1)

　　'コピー実行
　　Call CopySheet(l_xlsSht1, l_xlsSht2)
End Sub

Sub CopySheet(p_xlsSrc As Worksheet, p_xlsDst As Worksheet)
　　Dim l_xlsRngDst As Range

　　Set l_xlsRngDst = p_xlsDst.Cells

　　Application.CutCopyMode = False
　　'複写元をCtrl+C
　　p_xlsSrc.Cells.Copy

　　'形式を選択して貼り付け(書式)
　　Call l_xlsRngDst.PasteSpecial(xlPasteFormats)
　　'形式を選択して貼り付け(値)
　　Call l_xlsRngDst.PasteSpecial(xlPasteValues)

　　'コピーモード解除
　　Application.CutCopyMode = False

　　Call MsgBox("終了", vbInformation)

　　'出力先が選択された状態になっているので、先頭を選択して解除する
　　l_xlsRngDst.Cells(1).Select
End Sub

お礼

遅くなりましてすみません。
ご回答ありがとうございます。
問題はこの方法で解決できました。
またなにかあればお願いします。

回答No.6

rivoisu

そういう場合「規定のフォーマット」のシートは別のファイルに作成し
マクロからそのファイルを開いてデータを貼りつけてセーブする。

マクロの入っているブックはマクロの開始前と終了後は変化しないようにしておく。
そうしないとテストやデバッグが大変になる。また運用も無図解しくなる。

回答No.5

DOUGLAS_

＞シートのセルの値のみを別ブックのシートにコピーしたい
　やり方はいくつもあろうかと存じますが。。。

　良い方法が思い付かなければ、ＶＢＡ の途中で、数式の入っているセルを「値化」するというようなこともできますし、

１）テンプレート ブック で 全セル を選択。
２）[書式(O)] - [セル(E)] - [保護] タブ - [表示しない(I)] の チェックボックス を オン にする。
３）テンプレート ブック を一旦保存し、終了する。

４）ＶＢＡブック から テンプレート ブック を起動し、
＞売上のデータを外部DBから抽出し既定のフォーマットに整形
した後で、[ツール(T)] - [保護(P)] - [シートの保護(P)...] をかける。
５）新規ブック を開く。
６）テンプレート ブック ですべて選択し、コピー、新規ブック に貼り付ける。

と数式は コピー されません。

　もっと、簡単な方法があったかも知れませんが。。。

　なお、(１) ～ (３) は一度しておけば、ＶＢＡブック には、(４) ～ (６) の作業だけ記録しておけばよいです。

回答No.4

imogasi

#2です。
>シートの関数をすべて値にもどしてコピーは可能でしょうか。
シートのコピーでなくシートのデータのあるセル範囲(Range)の他ブック・他シートへコピー貼り付けで
形式を選択して貼り付けー値（ｘｌValue)の操作（１件１シート分）をして、マクロの記録を採ってみてください。
本番ではシートごとにそれを繰り返すコードを作る。For Each　sh In Worksheets
それができると、またコードがわかると思うが。

回答No.3

DOUGLAS_

#1 DOUGLAS_ です。

#2 さんのご回答を拝見していて思ったのですが、
＞その「worksheet」に シートモジュール が含まれない場合
＞その「worksheet」に シートモジュール が含まれる場合
という考え方は冗長でしたね。

　最初から、新規ブック を開いて、そこに
＞売上のデータを外部DBから抽出し既定のフォーマットに整形
すれば、一番簡単かと存じます。

　従って、

１）操作用の ＶＢＡ 専用の ブック を作る、あるいは、#1 さんがお書きのように、
＞Personal.xlsにVBAを保存

２）「既定のフォーマットに整形」された テンプレート 用の ブック を作る。

３）(１) の ＶＢＡ により、(２) を開き、
＞売上のデータを外部DBから抽出し既定のフォーマットに整形
した後で、(２) の ブック を別名で保存、あるいは、この時点で、
＞そのファイルを送信先各位の送信する

というのが、普通の考え方かも知れません。

お礼

ご回答ありがとうございます。
コピーを行う場合
日付を関数を使用して取得しているので
コピーしたシートを見ると日付欄は編集用のブックを参照してしまいます。
理想はシートのセルの値のみを別ブックのシートにコピーしたいのですが
その方法はありますでしょうか？

回答No.2

imogasi

手操作なら他ブックへのシートこぴーなどすれば済むのではないですか。
面倒だと言うのなら
（１）データだけのブックをVBAで別途作る。
（２）マクロをVBAで消去する。
以上は
http://www.keep-on.com/excelyou/2001lng4/200107/01070298.txt
にコード例が在る。
（３）Personal.xlsにVBAを保存すると、送信先にはPersonal.xlsは送られず、内容も見られず、
実行もされない。しかしシートモジュール（イベント関係など）があると、Personal.xlsには入ってなくて、元のブックにあり、見れたり実行されると思う。（もし標準モジュールを参照していると、エラーだが。）

お礼

いつも回答ありがとうございます。
マクロをVBAで消去は参考になる方法です。
ありがとうございます。
シートの関数をすべて値にもどしてコピーは可能でしょうか。
日付取得関数を使用しているのですが、
これがすべてのシートに^適用されているので
動作が重くなっているようです。
なにか方法があればお願いします。

回答No.1

DOUGLAS_

＞各worksheetの値のみをコピーする方法

　その「worksheet」に シートモジュール が含まれない場合の、簡単な手口として、下記の様な操作を [新しいマクロの記録] で記録してみましょう。

１）[ツール(T)] - [マクロ(M)] - [新しいマクロの記録(R)...] を クリック します。
２）当該「worksheet」の シートタブ を [右クリック] - [移動またはコピー(M)...] を クリック します。
３）[シートの移動またはコピー] ダイアログ - [移動先ブック名(T):] を「(新しいブック)」にし、[コピーを作成する(C)] の チェックボックス を オン にして、[OK] します。
４）新しく開いたブック（コピーされたもの）を適当な名前で保存して、終了します。
５）[ツール(T)] - [マクロ(M)] - [記録終了(R)...] を クリック します。

　以上で、

Sub Macro1()
　Sheets("worksheet").Select
　Sheets("worksheet").Copy
　ActiveWorkbook.SaveAs Filename:= _
　　"{保存先ファイルのフルパス}\適当な名前.xls", FileFormat:=xlNormal, _
　　Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
　　CreateBackup:=False
　ActiveWorkbook.Close
End Sub

というような コード を得ることができます。

　その「worksheet」に シートモジュール が含まれる場合は、新規の ブック を立ち上げて、シート全体 を コピペ する操作でも記録してみてください。