マクロ 値貼付 oguno

質問

Value = .Value という値を直接代入する方式に変更し早く処理をしたい。
そのようにこちらのマクロを変更したいのですが、ご教示下さい。

'未転記のデータがあればデータコピー
　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

回答No.4

ham_kamo · ベストアンサー

ham_kamoです。
前々回の質問で、私が日付のフォーマットが変わってしまう原因がわからないのでギブアップしたのですが、前回の質問で
WS2.Columns("B:B").NumberFormatLocal = "yy-mm-dd"
を値のコピーの「前」に入れたかどうか、oguno様にきちんと確認していませんでした。

上記の質問文にある箇所を、

'未転記のデータがあればデータコピー
　If WS1Mon > WS2Mon Then
　　If WS2.Cells(StartRow, 2).Value <> "" Then
　　　CopyToRow = WS2LastRow + 1
　　Else
　　　CopyToRow = StartRow
　　End If
　　WS2.Columns("B:B").NumberFormatLocal = "yy-mm-dd"
　　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

としてみてください。

なお、補足ですが値をコピーしたあとの、
　　WS2.Cells(CopyToRow, 2).Resize(WS1Mon - WS2Mon, 7).Select
は、コマンドボタンを押しでデータをコピーしたあと、コピーされたデータがどれかわかりやすくするように選択状態にしているので、処理自身にはあまり関係ありません。（PasteSpecialでは自動的に選択状態になるので、その挙動にあわせただけです）

これでうまくいかなかったら、

「マクロの中で、表示形式が yy-mm-dd の日付データの入ったセルの値を、別のシートへセルへ代入したところ、PCによってはフォーマットが m/d/yyyy 形式に変わってしまう（変わらないPCもある）。直前に NumberFormatLocal = "yy-mm-dd" を入れても動作は変わらない。原因として何が考えられるでしょうか」

という形で改めて質問した方が改めて質問された方がよいかと思います。

お礼

●＞WS2.Columns("B:B").NumberFormatLocal = "yy-mm-dd"
を値のコピーの「前」に入れたかどうか、oguno様にきちんと確認していませんでした。
　　　　－－－－－－－
はい、確認しましたが、所定の位置に入っていました。
●正しい構文を再確認させていただくことが出来ましたので、他のパソコンで試行してみたいと考えております。

●この質問に記載しました構文で使用ささていただきます。

ham_kamo様には、長時間にわたり、ご迷惑をおかけしましまし事、ご指導賜りた事、心よりお詫び、御礼申し上げます。　oguno

回答No.6

Wendy02

こんばんは。

KenKen_SPさんの紹介のあった元の英文から読んでみました。

XL2002: Date Formats Are Lost When You Programmatically Move Data from One Range to Another

現象
セルの1つの範囲から他の範囲にデータを移動するマクロを使用するときに、日付データを含むセルの書式は失われる。

原因
・ユーザーの地域設定が、いかなる国の短い日付書式でも、dd/mm/yyyy の設定が使用される。

・日付書式が、dd-mm-yyyy でコピーされる。

・マクロが、1つの範囲をもう1つの範囲に"＝"で設定するするか、配列の範囲を読み、そして、新しい範囲に出力する場合。

（VBEditor側のオリジナル書式に反映してしまう、ということでしょうね。）
-------------------------------

もし、それが事実なら、パッチ当てが原則なのでしょうけれども、貼り付ける場所の書式を欧州式(dd-mm-yyyy)にしておいて、再び、そこをローカル書式の設定すれば戻るはずです。

WS2.Cells(CopyToRow, 2).Resize(WS1Mon - WS2Mon, 7).NumberFormat ="dd-mm-yyyy"
' にしてから、貼り付け

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

でも、あえて、配列データで貼り付けること自体が目的なら、別ですが、Copy メソッドだって良いと思います。Application.ScreenUpDate =False を使えば、そんなに何分も違うはずはありません。

回答No.5

KenKen_SP

こんにちは。

ご参考までですが...

> 日付のフォーマットが変わってしまう

これが、Excel2002 の話なら、

[XL2002]
プログラムによってある範囲から別の範囲にデータを移動すると、
データの書式が失われる
http://support.microsoft.com/default.aspx?scid=kb;ja;821564

これかと。自動翻訳なので奇妙な日本語ですが、要は

　　VBA で Date 型の値を Variant 配列に格納し 別のセルに転記
　　すると日付書式が失われる（正確には英語圏の書式となる）

というものだったと思います...（ウロ覚え）

参照先の URL にあるとおり、この不具合はサービスパック３で
修正されてます。

Excel2000 の話なら、コントロールパネルの地域の設定あたりが
原因なのではないかと思います。

お礼

ありがとうございました。

●参考にさせていただき、勉強して見ます。

回答No.3

ham_kamo

こんにちは、前回回答したham_kamoです。
このような続きの質問をするときは、以前の回答のURLを書いておくか、これまでの経緯を要点を整理して書いた方がよいと思いますよ。

エクセルデータのリンク貼付（？）
http://oshiete1.goo.ne.jp/kotaeru.php3?qid=2783449

マクロ　値貼付　　oguno
http://oshiete1.goo.ne.jp/kotaeru.php3?qid=2801695

元々のマクロは私が書いたものです。最初、 Copy & PasteSpecial でやっていたのを、高速化のために .Value = .Value に書き換えました。それは、No.1のWendy02さんの回答と全く同じです。

しかし、.Value = .Value にすると、コピー元の日付がyy-mm-ddなのに、コピー先では m/d/yyyy 形式になってしまい、
WS2.Columns("B:B").NumberFormatLocal = "yy-mm-dd"
をマクロに追加しても改善されず、私には原因がわからなかったので Copy & PasteSpecial に戻した、という経緯です。（他の回答者への補足というか、これまでの経緯です）

それで、日付の形式が変わってしまう、という現象の原因はわかったのでしょうか？そのまま修正しても、また同じ現象が起こると思うのですが…）

補足

質問見ていただき、ご回答いただきました事感謝します。
●
＞このような続きの質問をするときは、以前の回答のURLを書いておくか、これまでの経緯を要点を整理して書いた方がよいと思いますよ。
・解りましたありがとうございます。
・質問者のエチケットとして、利口になりました。
------------
>また同じ現象が起こると思うのですが…）
・ME Eccel 2000では、YY-MM-DDで表示されます。
・私のパソコン（XP Eccel 2000）では、YY-MM-DDでは何故表示されないのか、他のパソコンであれば表示されるのか、再確認したくお願いいたします。
・それには、ご指導いただきましたマクロを、私が正しく修正しているのか自信が無くなってしまったので、お願いした次第です。
------------
●心情ご賢察の上宜しくお願いいたします。

回答No.2

noname#31387

サンプルです。全然勘違いなら無視して下さい。
シート1のB1～B最終行の値をシート2のB1～に
>Value = .Value という値を直接代入する方式に変更し早く処理をしたい。
が早いかどうか別にして、
オブジェクトのアクセス回数を減らすことかと。

Sub Test()
Dim myRng1 As Variant, LastRow As Long
Dim WS1 As Worksheet, WS2 As Worksheet
Set WS1 = Worksheets("Sheet1")
Set WS2 = Worksheets("Sheet2")
LastRow = WS1.Range("B" & CStr(Rows.Count)).End(xlUp).Row
myRng1 = WS1.Range("B1:B" & CStr(LastRow)).Value
WS2.Range("B1").Resize(LastRow, 1).Value = myRng1
Set WS1 = Nothing
Set WS2 = Nothing
End Sub

お礼

ありがとうございました

回答No.1

Wendy02

こんにちは。

速く処理するのは、本来は、.Value = .Value とする以前に、
Application.ScreenUpdating = False

　'実行

Application.ScreenUpdating = True

と挟んであげればよいはずです。

>Value = .Value という値を直接代入する方式に変更し早く処理をしたい。
もし、Value貼り付けするなら、試してはいませんが、以下のようにすればよいと思います。

'　　WS1.Cells(StartRow + WS2Mon, 2).Resize(WS1Mon - WS2Mon, 7).Copy
'　　WS2.Cells(CopyToRow, 2).PasteSpecial Paste:=xlValues

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

お礼

●
＞Application.ScreenUpdating = False
'実行
　Application.ScreenUpdating = True
--------
記述してあります。

●色々とご指導ありがとうございました。
今後とも宜しくお願いいたします。