マクロ 値貼付 oguno

質問

●日付表示をyy-mm-dd(07-03-04)と、したかったのですが構文1では「3/4/2007」の表示になってしまいました。

●構文2のように修正しましたら、希望の日付表示「07-03-04」になりました。

●修正箇所
構文1の「Value」を「Copy」と修正し、
「Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False」を追加しました。
ーーーーーーーーーーーーーーーーーーーーーーーー
●お願い
希望の日付表示にはなりましたが、記述の仕方が、これで正しいのかどうか、初心者のため自信がありません。
正しく記述しているのかどうか、ご検証をお願いいたします。
なお、構文１もこのサイトでご指導いただいたものです。
ーーーーーーーーーーーーーーーーーーーーーーーー

●構文１
'未転記のデータがあればデータコピー
If WS1Mon > WS2Mon Then
If WS2.Cells(StartRow, 2).Value <> "" Then
CopyToRow = WS2LastRow + 1
Else
CopyToRow = StartRow
End If
WS2.Cells(CopyToRow, 2).Resize(WS1Mon - WS2Mon, 7).Value = _
WS1.Cells(StartRow + WS2Mon, 2).Resize(WS1Mon - WS2Mon, 7).Value
WS2.Cells(CopyToRow, 2).Resize(WS1Mon - WS2Mon, 7).Select
Else
Selection.Select
End If

Application.CutCopyMode = False

●構文２
'未転記のデータがあればデータコピー
If WS1Mon > WS2Mon Then
If WS2.Cells(StartRow, 2).Value <> "" Then
CopyToRow = WS2LastRow + 1
Else
CopyToRow = StartRow
End If
WS2.Cells(CopyToRow, 2).Resize(WS1Mon - WS2Mon, 7).Value = _
WS1.Cells(StartRow + WS2Mon, 2).Resize(WS1Mon - WS2Mon, 7).Copy
WS2.Cells(CopyToRow, 2).Resize(WS1Mon - WS2Mon, 7).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
Selection.Select
End If

Application.CutCopyMode = False

回答No.3

ham_kamo · ベストアンサー

こんにちは、ham_kamoです。

WS2.Cells(CopyToRow, 2).Resize(WS1Mon - WS2Mon).NumberFormatLocal ="yy-mm-dd"

をコピーする前に入れても、PCによっては日付の形式が変わってしまう、ということなのでしょうか。何が原因なのか、申し訳ありませんが私にはやはりわかりません。（もっとスキルの高い方なら推測できるのかもしれませんが）

コントロールパネルの日付の設定は、どのようにするのがベスト、というのはありません。各自の好みです。マクロの中できちんとフォーマットを指定しておけば、その設定に左右されることはないはず…、だと思ってたのですが。でも形式が変わってしまうのですよね。しかしコンパネでは"yy-mm-dd"で設定されていて、マクロの中でも同じ指定をしているのに、「3/4/2007」という表示になるというのは、不可解です。

つい最近、VLOOKUPなどを多用した複雑な式を含んだ、やはり5000行くある表を、同じようにCopyしてPasteSpecialすると、30秒くらいかかった（自分的にはストレスでした）ので、.Value = .Value という値を直接代入する方式に変更したら劇的に早くなり、そのようにこちらのマクロも変更したのですが、数式が入ってなかったら5000行くらいあってもCopy＆PasteSpecialでもたいして時間はかかりません。

それで、前回の質問で詳細を聞かずに勝手にコピーの処理を変更してしまったのですが、C列～H列のデータというのは、複雑な数式などでなく、単なるデータなのでしょうか。もしそうであれば、CopyしてPasteSpecialするという方法でも、処理速度面でそんなに問題はないので、いったんそれに戻しましょう。

とりあえず再度マクロを全文転載します。

Private Sub CommandButton1_Click()
　Dim WS1 As Worksheet, WS2 As Worksheet
　Dim i As Integer, j As Integer, CopyToRow As Integer
　Dim CopyMonth As Integer, WS1Mon As Integer, WS2Mon As Integer
　Const StartRow As Integer = 6 'データが入力されている最初の行番号
　Set WS1 = Worksheets("入力画面") '月毎のシート名
　Set WS2 = Worksheets("年間成績") '年間のシート名
　Dim WS2LastRow As Integer '3/1追加

　'シート１が未入力（月の取得が不可能な場合）は中止
　If WS1.Cells(StartRow, 2).Value = "" Then Exit Sub

　Application.ScreenUpdating = False

　'シート保護解除
　WS2.Unprotect

　'月ごとのシートから転記する月を取得
　CopyMonth = Month(WS1.Cells(StartRow, 2).Value)

　'月ごとのシートから、その月の入力済みデータ数を取得
　'C列にデータが入れば自動的に日付が入るようにあらかじめB列に数式が
　'組み込まれているため、B列の中から空文字列でないセルをカウントする。
　For i = StartRow To WS1.Cells(Rows.Count, 2).End(xlUp).Row
　　　If WS1.Cells(i, 2).Value <> "" Then WS1Mon = WS1Mon + 1
　Next

　'年間のシートからその月の転記済みデータ数を取得
　If WS2.Cells(StartRow, 2).Value <> "" Then
　　WS2LastRow = WS2.Cells(Rows.Count, 2).End(xlUp).Row
　Else
　　WS2LastRow = StartRow
　End If
　For i = StartRow To WS2LastRow
　　If Month(WS2.Cells(i, 2).Value) = CopyMonth Then WS2Mon = WS2Mon + 1
　Next

　'未転記のデータがあればデータコピー
　If WS1Mon > WS2Mon Then
　　If WS2.Cells(StartRow, 2).Value <> "" Then
　　　CopyToRow = WS2LastRow + 1
　　Else
　　　CopyToRow = StartRow
　　End If
　　WS1.Cells(StartRow + WS2Mon, 2).Resize(WS1Mon - WS2Mon, 7).Copy
　　WS2.Cells(CopyToRow, 2).PasteSpecial Paste:=xlValues
　Else
　　Selection.Select
　End If

　Application.CutCopyMode = False

　'シート保護
　WS2.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

　Application.ScreenUpdating = True
End Sub

お礼

ありがとうございました。
QNo.2823108 マクロ　値貼付でご指導をいただけましたので、こちらは締め切りさせていただきます。

捕捉ばかりで、長くなると申し訳ないと思い、別質問に切り替えましたが、質問の仕方が不適切なようでした。
注意いたします。
ただ、ham_kamo様にご指導いただければ幸いと思いあのような質問の仕方しました心情はご賢察下さい。
本当にありがとうございました。

補足

ham_kamo様
＞再度マクロを全文転載します。
●ありがとうございました。
-------------
＞.Value = .Value という値を直接代入する方式に変更したら劇的に早くなり
●こちらの構文も勉強させていただきたいと思いますので、全文お願い出来ないでしょうか。
・尚回答は下記質問に転載してください。

QNo.2823108 マクロ　値貼付　　oguno
-------------
●これだけご指導いただきながら、ポイントが少なく申し訳ないと思い
新しい質問に致しました。

●連絡が途切れますと困りますので、QNo.2823108 マクロ　値貼付　　oguno にご回答いただけましたら、こちらを締め切らせていただきます。

●体調を崩し、遅くなりましたことお詫びいたします。　oguno

回答No.4

Wendy02

こんばんは。Wendy02です。

>・構文１では、同じ表示「3/4/2007」となりました。

コードをみると、内部データがシリアル値なら、単に、データが移動しているだけで、貼り付ける前に、書式設定をして、その上に、Value を貼り付けて、それがダメになるというのは、考えられません。シリアル値でないなら、また別です。その場合は、.Value そのままでは出来ません。

本来、別のプログラムで、ワークシート上のデータを、日付型のデータにしなければなりません。大方、そういう所に問題の解決があるような気がします。

結果で、こういうようになったと言っても、元のデータがどのようなデータかサンプルもみていないし、セルの書式も確認せずに話しを進めていますから、なんともいえない部分があります。

ただし、

>最終的な構文全文をご記載頂けないでしょうかお願い致します。

私のほうでは、コードが読めませんので、それは書けません。ただ、

ham_kamoさんがおっしゃるように、
>数式が入ってなかったら5000行くらいあってもCopy＆PasteSpecialでもたいして時間はかかりません。

でも良いかもしれません。

それと、

構文２の
WS2.Cells(CopyToRow, 2).Resize(WS1Mon - WS2Mon, 7).Value = _
WS1.Cells(StartRow + WS2Mon, 2).Resize(WS1Mon - WS2Mon, 7).Copy

このコードが通るのがヘンです。

WS1.Cells(StartRow + WS2Mon, 2).Resize(WS1Mon - WS2Mon, 7).Copy
この行をコピーしたという戻り値－True が返りますから、それが、
WS2.Cells(CopyToRow, 2).Resize(WS1Mon - WS2Mon, 7).Value　に、True の値が、貼り付けられるはずです。

お礼

Wendy02様
質問の表現が不適切な為、色々とご迷惑をお掛けいたしました。
構文１の方法で完成いたしました。
ありがとうございました。
追
体調を崩し、御礼が遅くなり申し訳ございません。　　oguno

回答No.2

ham_kamo

こんにちは、前回回答したham_kamoです。
Wendy02さんの回答で少し見えてような気がします。

値をValueでコピーすると、yy-mm-dd　が m/d/yyyy に変わってしまう、という現象は私の環境では再現しなかったので、原因がわからずギブアップしたのですが、NumberFormatLocal = "yy-mm-dd" をコピーの後に入れていたのが悪かったようですね。

私の環境で再現しなかったのは、Wendy02さんの書かれている、

> それと、本来、コンパネの地域の日付設定から、そのようになったような気がします。

ここの設定がogunoさんと異なっていたかもしれません。

Wedny02さんの回答を少し修正させていただくと、Ｂ列以外の列は数値として表示しないといけないので、

WS2.Cells(CopyToRow, 2).Resize(WS1Mon - WS2Mon).NumberFormatLocal ="yy-mm-dd"

とすればよいと思います。

なお、先の質問で私が最初に書いたVBAは、構文２のようにCopyしてPasteSpecialする方法でしたが、データ数が5000行を超えるということで、この方法では時間がか可能性が高いと思い、配列のValueで直接代入する方法に変更しました。それから日付の形式がおかしくなってしまったのですね。

あえて構文２で方法で記述するのであれば、

WS2.Cells(CopyToRow, 2).Resize(WS1Mon - WS2Mon, 7).Value = _

この１行は不要です。（本当にエラーが出ないのが不思議です）
いったんSelectする必要はなく、直接起点となるセルを指定して貼り付ければいいので、

WS1.Cells(StartRow + WS2Mon, 2).Resize(WS1Mon - WS2Mon, 7).Copy
WS2.Cells(CopyToRow, 2).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

でいいかと思います。（最後の行は、コピーした範囲の枠が点滅するのを解除するコードです）

補足

この質問にお目を止めて頂き有難うございます。
---------
●　>コンパネの地域の日付設定の件
Wedny02さんにも御願いいたしましたが、ham_kamo様にもご指導頂きたく御願い申し上げます。
---------
・設定した記憶は無いのですが、yy-mm-ddに設定していました。
・本来の質問と違ってしまい申し訳ないのですが、どのように設定するのがベストなのでしょうか。
御指導いただければ幸でございます。
・設定が違うと結果が異なるのであれば、設定を合わさせていただき、テストをしご報告申し上げます。

●取敢えず、yy-mm-ddとyyyy/mm/ddの設定で、テストしましたが、
「XP　Excel　2002」
・構文１では、同じ表示「3/4/2007」となりました。
・構文２方式ではでは、「07-03-04」となりました。
「ME　Excel　2000」
・構文１では、「07-03-04」となりました。
・構文２方式ではでも、「07-03-04」となりました。
＊下記の件御願いできれば、落ち着いてもう一度試行し整理いたします。

●上記のようになりましたが、色々と修正いたしましたので、正直正しく修正出来ているのか、自信が無くなりました。
・またかと、お叱りを受けるかもしれませんが、最終的な構文全文をご記載頂けないでしょうかお願い致します。
・構文１タイプ（高速処理）
・構文２タイプ
お手数をお掛け致しますが、説明コメントは出来るだけ詳細にお願い出来れば幸でございます。

勉強の為にも、２ファイル作成し・構文１タイプ（高速処理）と・構文２タイプを実感してみたいと思っております。

---------
身勝手な御願いばかりで申し訳ございませんが、よろしく御願い申し上げます。　　　　oguno

回答No.1

Wendy02

こんにちは。

Excelでは、日付データは、貼り付ける前に、書式を整えるというのが、原則なんです。貼り付けた後ですと、データに、型のキャスティングが起きてしまって、戻せなくなってしまいす。

しかし、構文２は、よく、それでエラーが出ないか、不思議だと思います。

今回は、.Value = .Value で、配列の貼り付けになっているので、そういうことになるので、もし、Copy メソッドなら、書式も貼り付きますから、そのような現象は起こらないはずです。

そのままを活かすには、
貼り付ける前に、

WS2.Cells(CopyToRow, 2).Resize(WS1Mon - WS2Mon, 7).NumberFormatLocal ="yy-mm-dd"

としてから、構文1を行えばよいはずです。

それと、本来、コンパネの地域の日付設定から、そのようになったような気がします。
なお、関係はありませんが、VB Editor 内は、確か、日本など向けは、米語仕様になっていたような気がします。

補足

御指導有難うございます。
●＜Excelでは、日付データは、貼り付ける前に、書式を整えるというのが、原則なんです。

言い訳になりますが初心者の為、よく判りませんでした。
肝に銘じます。
お叱り有難うございました。

●＜本来、コンパネの地域の日付設定から、そのようになったような気がします。

・設定した記憶は無いのですが、yy-mm-ddに設定していました。
・本来の質問と違ってしまい申し訳ないのですが、どのように設定するのがベストなのでしょうか。
御指導いただければ幸でございます。
・設定が違うと結果が異なるのであれば、設定を合わさせていただき、テストをしご報告申し上げます。　　　oguno