エクセルVBAで書式と値の貼付けにつて

質問

エクセル2007VBAで新規ファイルを作る場合のコピー、貼り付けで質問しましたが
式も全て貼り付けになるとＵＳＢメモリーで持ち出した場合、エラーとなります。
それで値と書式のみ貼り付けする様下記の様に書き直しましたが、.PasteSpecialでメソッドまたはデータメンバーが見つかりませんとなります。
ぐぐっててヘルプを見ますが解決出来ません。どなたがご教授お願いします。

元の式
Sub DGCopy()
Workbooks.Add
With ThisWorkbook
.Sheets(5).Cells.Copy Sheets(1).Cells
Sheets(1).Select
Sheets("Sheet1").Name = "電気代"
.Sheets(6).Cells.Copy Sheets(2).Cells
Sheets(2).Select
Sheets("Sheet2").Name = "ガス代"
F = Application.GetSaveAsFilename(FileFilter:="Excelブック (*.xls),*.xls)")
If F = "False" Then Exit Sub ' ダイアログでCancelをクリックした場合
ActiveWorkbook.SaveAs Filename:=CStr(F)
End With
End Sub

書き直した式
Sub DGCopy()
Workbooks.Add
With ThisWorkbook
Sheets(5).Select
Cells.Selection.Copy
Sheets(1).Selection
.PasteSpecial Paste:=xlPasteFormats　←エラー部分
.PasteSpecial Paste:=xlPasteValues
Sheets("sheets1").Name = "電気代"
Sheets(6).Select
Cells.Selection.Copy
Sheets(2).Selection
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteValues
Sheets("sheets2").Name = "ガス代"
F = Application.GetSaveAsFilename(FileFilter:="Excelブック (*.xls),*.xls)")
If F = "False" Then Exit Sub
ActiveWorkbook.SaveAs Filename:=CStr(F)
End With
End Sub

回答No.4

keithin · ベストアンサー

なるほど。

元のご質問：
＞式も全て貼り付けになるとＵＳＢメモリーで持ち出した場合、エラーとなります。

これが何故なのか，が判ってないからですね。
（といっても実はこちらも，アナタが一体どんな・何を「エラー」といってるのか，ホントの所はさっぱり不明なんですが，一応ご説明の様子からかろうじて推測しています）

あなたが言ってる"エラー"というのは必ずしも正しい表現じゃありませんが，「関数式があるから」エラーになるんじゃありません。
シートを（数式を）コピーしたときに，元に残しておいたシートに参照（リンク）が残っている時に，「他のパソコンに持って行くと」問題が起こります。
回答したのは，その「問題が起きる式」だけを潰し，問題にならない他の計算式（たとえばシート内で隣のセルを参照する，SUMを求める，みたいな単純なヤツ）はそのまま残すマクロです。

まぁでもアナタの思うとおりどうしてもヤリタイなら，次のようにしてみたらどうでしょう。
＃スミマセンが，No.1の方へのご質問はNo.1の方にお願いします。

sub macro3
　dim w as workbook
　dim myfile as variant
　set w = workbooks.add

’コピーし，数式は全て潰す。
’アナタの提示のマクロは「どこから」コピーしたいのかいつも違うので，適切に漏れなく直してください
　with w.worksheets(1)
　　.name = "電気代"
　　thisworkbook.worksheets(シートの番号).cells.copy .range("A1")
　　.usedrange.value = .usedrange.value
　end with

　with w.worksheets(2)
　　.name = "ガス代"
　　thisworkbook.worksheets("シート名").cells.copy .range("A1")
　　.usedrange.value = .usedrange.value
　end with

’保存
　myfile = application.getsaveasfilename(fileFilter:="Excelブック (*.xls),*.xls)")
　if myfile = False then exit sub
　activeworkbook.saveas filename:=myfile
end sub

お礼

ありがとうございます。
ホントにもうおっしゃるとおりで（全てお見通しみたいですね）いつもあせって見間違えてたりやり間違えてたりですみませんです。
リンクも最初見た時、”リンク”ん、何でっと思ったのとtype:=xllinktypeexcellinksなんて見たこともなかったので、ネットで調べたらちゃんと出てくるのに、最初はしなかったんですよね。これってホント、駄目ですね。それで値と書式の貼り付けにばかり気を取られてて後から考えれば、そうだリンクだ、こんな簡単な事落ち着いて考えたらこんな迷わなくて済んだのに。
keithinさんがいつも適切な答え出していただいてるのだから疑う余地はないとわかっているのにもです。
今後はもっと落ち着いて良く考えるようにします。
どうもありがとうございました。

回答No.3

keithin

ん？？　どういうことでしょうか？

なんでわざわざこんな説明をしなきゃならないんでしょうか，と思っちゃいますが，

＞値と書式だけ貼り付けしたいのですが、

当然対応済みです。
試してもない，回答も途中までしか見てないということですね。

お礼

すいません、さっきも試したのですが値だけでなく式も貼り付けになってるみたいなのですが。
今も試したらやはり値でなく式が貼り付けられてるのですが・・・。
こちらがやり方間違っていたらすいません。
それからkeithinのはリンクの切断，値化　(new)の部分が高度すぎてさっぱりわからないのですが。
それで自分の作った式を直して頂きたくてお聞きしたのです。
気を悪くされましたら、すみません。
よろしくお願いします

補足

ごめんなさい。出来てます、どこを見ていたんだか、本当にすいません。見落としてました。
.linksourcesリンクの解除ってそういう事だったんですね。
それからやはり、自分で作った式もどこをどうしたら動くのか知りたいので直していただけないでしょうか？
どうかよろしくお願いします。

回答No.2

keithin

＃もうちょっと簡単なマクロがアドバイスされていたと思いましたので，それを応用して。

sub macro1()
　dim a, aa
　dim myFile as variant

’コピー
　worksheets(array(5,6)).copy
　worksheets(1).name = "電気代"
　worksheets(2).name = "ガス代"

’リンクの切断，値化　(new)
　on error resume next
　aa = activeworkbook.linksources(type:=xllinktypeexcellinks)
　for each a in aa
　activeworkbook.breakLink name:=a, type:=xlexcellinks
　next

’保存
　myfile = application.getsaveasfilename(fileFilter:="Excelブック (*.xls),*.xls)")
　if myfile = False then exit sub
　activeworkbook.saveas filename:=myfile
end sub

お礼

解答ありがとうございます
うーーん、array使いますと全部コピーされますよね
値と書式だけ貼り付けしたいのですが、よろしくお願いします。

補足

no,1の方の言われた事で修正すると下記の様になりますが
下記のところでやはりオブジェクトが必要ですと出ます。
限界ですのでよろしく修正お願いします。

Sub DGCopy()
Workbooks.Add
With ThisWorkbook
Sheets(1).Select
Cells.Copy
Workbook.Sheets(1).PasteSpecial Paste:=xlPasteFormats
Workbook.Sheets(1).PasteSpecial Paste:=xlPasteValues
Sheets("sheets1").Name = "電気代"
Sheets(2).Select
Cells.Copy
Workbook.Sheets(2).PasteSpecial Paste:=xlPasteFormats
Workbook.Sheets(2).PasteSpecial Paste:=xlPasteValues
Sheets("sheets2").Name = "ガス代"
F = Application.GetSaveAsFilename(FileFilter:="Excelブック (*.xls),*.xls)")
If F = "False" Then Exit Sub
ActiveWorkbook.SaveAs Filename:=CStr(F)
End With
End Sub

回答No.1

Cor_moriyan

ん～、ボロボロですね(^^;

.PasteSpecial ですが、コレは今 ThisWorkbook に対して行っていますよね？.PasteSpecial が指定できるのは Range か Sheet に対してです。

それから、Cells.Selection.Copy　もおかしいです。Selection は Window か Application に対してしか指定できません。

Workbook.Add　の後で Sheets(5).Select していますね。新たにブックを作って、そのブックの Sheets(5) をSelectしようとしていますが、初期状態で「新規ブック作成時は５シート作る」ように設定しているのでしょうか？していなければ、ココもエラーです。

申し訳ないですが....このソースを見ただけだと何をしたいのか分からないほど(^^;ボロボロですので。
もう一度、一から考え直した方が良いと思いますよ。
それと。
まず「コンパイル」で文法を確認してから実行しましょうね。
ExcelVBAのエディタは、入力時に使用できるメソッド一覧等を表示してくれます。活用して下さい。

＃Workbook.Add で新規ブックを作成した場合、その後Activeになっているのは作成した新規ブックです。
＃Workbookを指定せず、いきなり Sheets を記述した場合、対象になるのは Active のブックです。
＃Worksheetを指定せず、いきなり Cells を記述した場合、対象になるのは Active のブック・シートです。

お礼

解答ありがとうございます。
これを書いた後、.PasteSpecial が指定できるのは Range か Sheet に対してです。を見つけました。
それからSelection は Window か Application に対してしか指定できません。
そうなんですね、ネットでVBAの基本とか読んで勉強してるんですが、そんな事全然かかれてないんですよね。
難しいですね。すみません、やりたい事はhttp://oshiete.goo.ne.jp/qa/7110078.htmlなんですけど。

補足

Workbook.Add　の後で Sheets(5).Select していますね。新たにブックを作って、そのブックの Sheets(5) をSelectしようとしていますが
今開いているブックに新たにブックを作ってSheets(5).Selectで元のブックのSheets(5)って言うことではないのでしょうか？これで動いていたんですが？
よろしくお願いします。
入力時に使用できるメソッド一覧等を表示、ctrl+スペースで表示されるんですね。
わかりました。ありがとうございます。活用します。