Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
2787 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【65934】シートコピーで、シートの値を取得したい ゆめこ 10/7/9(金) 4:28 質問[未読]
【65938】Re:シートコピーで、シートの値を取得したい Abebobo 10/7/9(金) 9:35 発言[未読]
【65939】Re:シートコピーで、シートの値を取得したい 超初心者 10/7/9(金) 9:59 発言[未読]
【65940】Re:シートコピーで、シートの値を取得したい teian 10/7/9(金) 10:12 発言[未読]
【65966】Re:シートの範囲コピーで、セルの値を取得... ゆめこ 10/7/11(日) 4:32 質問[未読]
【65967】Re:シートの範囲コピーで、セルの値を取得... りん 10/7/11(日) 7:31 発言[未読]
【65982】Re:シートの範囲コピーで、セルの値を取得... ゆめこ 10/7/12(月) 21:30 質問[未読]
【65985】Re:シートの範囲コピーで、セルの値を取得... りん 10/7/13(火) 7:01 発言[未読]
【66004】Re:シートの範囲コピーで、セルの値を取得... ゆめこ 10/7/14(水) 19:56 お礼[未読]
【65934】シートコピーで、シートの値を取得したい
質問  ゆめこ  - 10/7/9(金) 4:28 -
引用なし
パスワード
   ウィンドウスXP　エクセル2007、ＶＢＡ初心者です。

あるエクセルファイルの中の、任意の１枚のシートをシートコピーして独立させ、メールで各部署に回覧したいので、以下の用なマクロを設定してみました。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

Sub ワークシートを新規ブツクにコピー()
　
　　Worksheets("元のsheet").Copy
　
　　ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & fn & "新しいsheet 回覧用.xlsx "　
　　
End Sub
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
シートコピーや、ファイル名の取得は上手くいくのですが、
元シートが、vlookupなど、他シートを参照して作成されているため、
回覧用のシートにも参照式がコピーされてしまいます。
計算式はコピーせずに、シート全体の値のみをコピーしたいと思い、
以下のように変更してみましたが、デバックとなりました。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

Sub ワークシートを新規ブツクにコピー()
　
　　Worksheets("元のsheet").Copy
　　Selection.PasteSpecial Paste:=xlPasteValues

　　ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & fn & "回覧用.xlsx "
　
End Sub

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

どこを直せばよいのか分からず、困っています。
コピー元と、コピー先がきちんと指定できていないということでしょうか。

どうぞ宜しく御願いします。
【65938】Re:シートコピーで、シートの値を取得し...
発言  Abebobo  - 10/7/9(金) 9:35 -
引用なし
パスワード
   Sub ワークシートを新規ブツクにコピー()

Dim C_wb　　As Workbook
Dim New_B　 As Workbook

Set C_wb = ThisWorkbook　　'コピー元
Set New_B = Workbooks.Add　'まず、ブックを作る
C_wb.Sheets("Sheet1").Cells.Copy
New_B.Sheets("Sheet1").Cells.PasteSpecial Paste:=xlPasteValues

New_B.SaveAs Filename:=ThisWorkbook.Path & "\" & "AA" & "回覧用.xlsx "
　
End Sub

今回の場合、こんな手順だと思います
【65939】Re:シートコピーで、シートの値を取得し...
発言  超初心者  - 10/7/9(金) 9:59 -
引用なし
パスワード
   ▼ゆめこ さん：

こんな感じでも良いかもです。

> Worksheets("Sheet1").Copy
　Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues
　Application.CutCopyMode = False

　ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & fn & "回覧用.xlsx "
【65940】Re:シートコピーで、シートの値を取得し...
発言  teian  - 10/7/9(金) 10:12 -
引用なし
パスワード
   新しいブックへシートごとコピー作成した後に、
今度はその作成されたシートの数式を外すという手順がいいと思います。

以下は、現在のアクティブシートを新ブックへコピーして、
そちらで数式を外す例です。

ActiveSheet.Copy
With Application.Workbooks
　　With .Item(.Count)
　　　　With .ActiveSheet.UsedRange
　　　　　　.Copy
　　　　　　.PasteSpecial xlPasteValues
　　　　　　.Item(1).Select
　　　　End With
　　　　Application.CutCopyMode = False
'　　　　.SaveAs 〜略
　　End With
End With
【65966】Re:シートの範囲コピーで、セルの値を取...
質問  ゆめこ  - 10/7/11(日) 4:32 -
引用なし
パスワード
   皆様、大変ありがとうございました。

どの方法も勉強になりました。
今回は、超初心者 さんのコードを使わせていただき、
無事に解決いたしました。ありがとうございました。

さらにお伺いしたいのですが、
今度は、あらかじめAシートの後ろに、ＡシートをコピーしたＢシートを作っておいて、
元シートＡの一部の範囲の値だけをコピーして、
すでにあるＢシートの同じセル位置にに貼り付けたいのです。

例えば、Aのシートの　B2:Y100の値をコピーして、
隣のシートBのB2:Y100にペーストしたく、
マクロコードをボタンに登録しようとしたののですが
「セルの大きさが同じでないと実行できない」とのエラーメッセージが表示されます。
シートBは、シートAの見出しタグをドラッグしてコピーをして作成したものですが、シートBのセルの大きさはシートＡと同じのはずなのですが・・・。

シートＡとシートBはあらかじめ存在させておきたいので、
教えていただいたシートコピーのマクロは使えません。
ためしに、一つ一つのセルをコピーしてペーストすることはできるのですが、
大きな範囲を設定すると上手くいきません。
コピーしたい部分が部分的にたくさんあり、
個別の指定をしてゆくと、マクロ式があまりにも膨大になり、
もし、元シートに一行でも挿入されると、
コードの変更が大変です。
できれば範囲していをしてペーストしたくて、
あれこれ試してみるのですが、上手く行きません。
何か対応方法はありませんでしょうか。

どうぞ宜しく御願いします。
【65967】Re:シートの範囲コピーで、セルの値を取...
発言  りん E-MAIL  - 10/7/11(日) 7:31 -
引用なし
パスワード
   おはようございます。

>例えば、Aのシートの　B2:Y100の値をコピーして、
>隣のシートBのB2:Y100にペーストしたく、
>マクロコードをボタンに登録しようとしたののですが
>「セルの大きさが同じでないと実行できない」とのエラーメッセージが表示されます。
>シートBは、シートAの見出しタグをドラッグしてコピーをして作成したものですが、シートBのセルの大きさはシートＡと同じのはずなのですが・・・。

先範囲.Value = 元範囲.Valueで、値貼り付けと同じような処理になります。

元の質問が回覧用にシートをコピーした新しいブックの作成だったので、
Sub TEST()
　 Dim wb As Workbook
　 Worksheets("A").Copy '新しいブックにシートをコピー
　 '
　 Set wb = Application.ActiveWorkbook '新しいブック
　 '
　 With wb.Worksheets("A").UsedRange
　　 .Value = .Value '式を値に変える
　 End With
　 'このブックの名前に月日時分を付け加えたファイル名を設定
　 wb.SaveAs Replace(ThisWorkbook.FullName, ".xls", Format(Now(), "回覧用_mmddhhnn") & ".xls")
End Sub
こんな感じです。
ファイル名はかぶらないように適当につけただけなので、無視してください。
【65982】Re:シートの範囲コピーで、セルの値を取...
質問  ゆめこ  - 10/7/12(月) 21:30 -
引用なし
パスワード
   ▼りん さん：
▼りん さん：

ありがとうございます。

>先範囲.Value = 元範囲.Valueで、値貼り付けと同じような処理になります。

早速、役立てさせていただきました。
ありがとうございました。

さらにお伺いしたいのですが、
ブックに月日時分を付け加えたファイル名を設定できるのであれば、
例えば、元データのＱ１のセルに入力されている任意の日付を
ファイル名に取り込むことも可能だと思い、

　ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & fn & ".xlsx ", Format(Range("Q1").Value, "回覧用_yyyymmdd")

としてみました。
入力当日の日付ではななく、Ｑ１の日付の値を取得することになると思うのですが、上手くいきません。
保存場所は、同一フォルダの中に別シートとして存在させたいのですが・・・。
　　
どこが間違っているか、教えていただければ有り難いです。
宜しく御願いいたします。
【65985】Re:シートの範囲コピーで、セルの値を取...
発言  りん E-MAIL  - 10/7/13(火) 7:01 -
引用なし
パスワード
   ゆめこ さん、おはようございます。
>ブックに月日時分を付け加えたファイル名を設定できるのであれば、
>例えば、元データのＱ１のセルに入力されている任意の日付を
>ファイル名に取り込むことも可能だと思い、
>ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & fn & ".xlsx ", Format(Range("Q1").Value, "回覧用_yyyymmdd")

fnにどんな文字列が入っているかわかりませんが、

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & fn & "回覧用_" &
Format(Range("Q1").Value, "yyyymmdd") & ".xlsx "

こうかなあ。
あと日付が疑わしいのでしたら、
　 MsgBox Range("Q1").Address(external:=True)
で、Rangeの前を省略時に正しいブックとシートを参照できているかも確認してみてください。
【66004】Re:シートの範囲コピーで、セルの値を取...
お礼  ゆめこ  - 10/7/14(水) 19:56 -
引用なし
パスワード
   ▼りん さん：

ありがとうございます。
思いどうりのファイルを作成することが出来ました。

本当にありがとうございました。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
2787 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free