[[20140328104656]]　『別シートのデータをコピーして貼り付けたい』（ままん）　ページの最後に飛ぶ

『別シートのデータをコピーして貼り付けたい』（ままん）

sheet1のA列からD列にデータを貼り付けます。
データを貼り付けられたA列と一致する文字列をSeet2のA列から探します。
一致したSeet2の行のB列からP列をコピーし、
Seet1のE列からS列に貼り付ける。

Sheet2には製品ごとに入力する項目かどうかの情報が入力されています。
なので、数式を入れて参照すると数式の上から入力する事になり、次に入力する時にフィル出来ないためマクロにしたいです。

Sheet1

	A	B	C	D	E	F	G	H	I	J	K	L	M	
1	製品１	1月1日	2月1日	10002	-		-	-			-			←ｼｰﾄ2、1行目を貼り付け
2	製品２	1月1日	1月19日	c1098		-			-	-				←ｼｰﾄ2、2行目を貼り付け
3	製品１	2月10日	2月21日	3009	-		-	-			-			←ｼｰﾄ2、1行目を貼り付け
4	製品10	2月9日	2月18日	4239	-	-	-	-	-	-	-	-	-	←ｼｰﾄ2、8行目を貼り付け

Sheet2

	A	B	C	D	E	F	G	H	I	J	K
1	製品１	-		-	-			-			-
2	製品２		-			-	-				
3	製品３	-		-			-				
4	製品４			-	-		-		-		-
5	製品５	-		-				-			
6	製品６	-		-						-	
7	製品７	-		-		-					-
8	製品１０	-	-	-	-	-	-	-	-	-

よろしくお願いします。

＜使用 Excel：Excel2007、使用 OS：WindowsVista ＞

 ＞Sheet2には製品ごとに入力する項目かどうかの情報が入力されています。 
 ＞なので、数式を入れて参照すると数式の上から入力する事になり、次に入力する時にフィル出来ない
 の記述から

 Sheet1は4行目以降次々にデータが追加されていく
 E列以降の「－」は、入力の目安にするために表示し、実際にそのセルにデータを入力する前には削除する。

 と想定します。

 マクロで自動的にコピーさせる場合
 　どのセル(行)は反映させて、どのセルは反映させない（そのままにしておく）
 等考えないといけない様に思いますので
 いっそのこと、条件付き書式でセルの色を変えて目立たせる様にするのはどうですか？
 　
（HANA） 2014/03/28(金) 14:08

HANAさん

回答ありがとうございます。

「－」は入力しない項目の目安です。
今は
=IF(A20="","",VLOOKUP(A20,Sheet2!A:O,2,FALSE))
とセルに数式が入力されています。
「－」でない空白のセル（数式の上から）に入力しています。
「－」と表示されるセルには入力しません。

どのセル(行)は反映させて、どのセルは反映させない（そのままにしておく）
とはどういうことでしょうか？

数式を組む前は、Seet1のA列に入力されたときに入力された文字列と一致するセルの隣のセルから数行コピーして貼り付けしていました。
条件付き書式でセルを目立たせたとしても、ｵｰﾄﾌｨﾙができないですよね？
数式の上から入力すると数式は消えてしまいませんか？
次にデータを追加した際にｵｰﾄﾌｨﾙできなくなると思うのですが…
（ままん） 2014/03/31(月) 10:32

 入力【しない】項目の目安ですか。

 表が完成した時に「-」の表示が必要かどうか　が問題になってきそうです。
 必要でないなら、入力する項目の方のセルに色を付けて目立たせるのでも
 良さそうに思います。

 具体的には
 ご呈示の式はE20セルに入っていると思いますので
 E20セルの条件付き書式の設定で
 「数式を使用して、書式設定するセルを選択」を選び
 =AND(E20="",VLOOKUP($A20,Sheet2!$A:$O,COLUMN(B20),FALSE)<>"-")
 の式をいれて、セルの色を設定して下さい。

 すでにセルに入力されている数式は、削除して下さい。

 その後、E20セルを必要範囲にコピーして下さい。

 A20には「製品1」が入力されているので
 まず、F20,I20,J20,L20,M20 のセルに色が付きます。
 各セルに何か入力すると、セルの色は消えます。

 ＞どのセル(行)は反映させて、どのセルは反映させない（そのままにしておく） 
 ＞とはどういうことでしょうか？ 
 マクロを実行するタイミングと、その範囲をどうするのかが分からない　です。
 たとえば
 　A列に入力が終わったら【人が】マクロ実行の指示を与え(マクロ実行ボタンを押す　等)
 　マクロは、E列以降のすべてのデータを削除して「-」を反映させる
 であれば、特に悩まずに出来ると思います。

 たとえば、
 　マクロ実行ボタンを押さなくても、勝手に実行して欲しい。
 　　　数式がコピーしてあれば、A列に入力したとたんに E列以降に「-」が表示されますよね。
 　20行目から23行目までは昨日入力が済んでいるので、今日入力したE24以降に「-」を反映させたい。
 　　　A列の入力をした人が、数式をコピーすると思いますので　どの範囲にコピーすれば良いか
 　　　当然知っていると思うので、現在は当たり前に出来ていると思います。
 等あれば、もう少し複雑になると思います。

 ＞数式の上から入力すると数式は消えてしまいませんか？ 
 私はこんな時は、数式を入れておくセルを作って　そこからコピーする様にしています。

 マクロを実行した時に
 20行以降でA列の入力がある行までの E:M列のセルに数式を埋め込む
 コードであれば
 '------
Sub TEST()
    Dim mxr As Long
    mxr = Range("A" & Rows.Count).End(xlUp).Row
    Range("E20:M" & Application.Max(20, mxr)).Value = _
        "=IF($A20="""","""",VLOOKUP($A20,Sheet2!$A:$O,COLUMN(B20),FALSE)&"""")"
End Sub
 '------
 になります。

 動かしてみてもらって、問題点を教えてもらっても良いかもしれません。
 　
（HANA） 2014/03/31(月) 11:28

HANAさん

回答ありがとうございます。

私の説明の仕方が悪かったみたいです。

「sheet1のA列からD列にデータを貼り付けます。」
はたとえ話です。

今までのデータが何十行も入力してあって
新しいデータを一番下の行に追加するってことです。
毎回同じところにデータを貼り付けるわけではないです。

なので、セルが変更になったときにマクロが動くと便利です。

自力で作ってみました。

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim COD As String

    Set f = Sheets("Sheet2").Range("A1:A1000").Find(Target.Value)

    Sheets("Sheet2").Range(f.Offset(0, 1), f.Offset(0, 16)).Copy Sheets("Sheet1").Range(Target.Offset(0, 4), Target.Offset(0, 19))

End Sub

エラーが出てしまいます。
オブジェクト変数または With ブロック変数が設定されていません
と出ます。
どうすればエラーを解消できますか？
よろしくお願いします。
（ままん） 2014/04/03(木) 11:01

解決しました。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim COD As String

If Intersect(Target, Range("A:A")) Is Nothing Then

        Exit Sub
    Else

        Set f = Sheets("Sheet2").Range("A1:A1000").Find(Target.Value)

        Sheets("Sheet2").Range(f.Offset(0, 1), f.Offset(0, 15)).Copy
        Sheets("Sheet1").Range(Target.Offset(0, 4), Target.Offset(0, 18)).PasteSpecial Paste:=xlPasteValues

    End If       
End Sub

としました。

次の問題が発生しています。
複数行同時に変更（貼り付けた）場合、うまくいかないです。
こういうときはどうすればよいでしょうか？
よろしくお願いします。

（ままん） 2014/04/03(木) 11:51

 FOR EACH で回しながら一つずつ処理していくと良いかもしれません。

 '------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim f As Range, MyRange As Range
    If Intersect(Target, Range("A:A")) Is Nothing Then
        Exit Sub
    Else
        For Each MyRange In Target
            Set f = Sheets("Sheet2").Range("A1:A1000").Find(MyRange.Value, LookAt:=xlWhole)
            If Not f Is Nothing Then
                Application.EnableEvents = False
                    Sheets("Sheet2").Range(f.Offset(0, 1), f.Offset(0, 15)).Copy
                    Sheets("Sheet1").Range(MyRange.Offset(0, 4), MyRange.Offset(0, 18)).PasteSpecial Paste:=xlPasteValues
                Application.EnableEvents = True
            Set f = Nothing
            End If
        Next
    End If
End Sub
 '------
 　
（HANA） 2014/04/03(木) 13:40

ありがとうございます。

また質問なのですが…
別のセルが変更された場合に別のマクロを動かしたい場合はどうすればよいのでしょうか？

B1のセルは入力規則が設定されており、
ドロップダウンリストから選択するようになっています。

B1で選択した文字列と一致するユーザー設定のビューを表示させたいのですがうまくいきません…

        If Intersect(Target, Range("B1")) Is Nothing Then
        Exit Sub
      Else

      ActiveWorkbook.CustomViews(range("B1")).Show

 End If

こんな感じで考えました。
CustomViews(range("B1"))をどうすればよいかわからないです。
また、先のコードのどこに挿入すればよいでしょうか？

よろしくお願いします。

（ままん） 2014/04/04(金) 10:58

 ↓の所を
 If Intersect(Target, Range("A:A")) Is Nothing Then
     Exit Sub
 Else

 ↓な感じに変更
 If Not Intersect(Target, Range("A:A")) Is Nothing Then

 If Intersect(Target, Range("B1")) Is Nothing Then
 の方も同様に。

 今は　決められた範囲の変更でない場合　すぐに処理を終了(Exit Sub)しています。
 それを、決められた範囲の変更だった場合に、処理をする。　に変更してみて下さい。

 Target, Range("A:A") に共通部分が無かったら(Nothing)、処理を終了(Exit Sub)・・・ではなく
 Target, Range("A:A") に共通部分が無かったら(Nothing) で無かったら(Not)、処理をする。
 　
（HANA） 2014/04/04(金) 11:50

ありがとうございます。

ActiveWorkbook.CustomViews(range("B1")).Show
ではうまく表示されないです。

エラーが出ないので？？？ってなってます。

（ままん） 2014/04/04(金) 12:32

 すみません、見落としてました。
 エラーが出ないという事なので、違っているかもしれませんが
 とりあえず
    ActiveWorkbook.CustomViews(Range("B1").Value).Show
 にしてみて下さい。

 こちらでは動きました。

 また、ステップ実行して
 コードが実行されているか確認して下さい。

 コード内の先頭の方に一行挿入して「Stop」と書いた行を作って下さい。
 マクロが実行されると、その行で一旦止まりますので [F8]を押して
 一行ずつ実行しながら、
    ActiveWorkbook.CustomViews(Range("B1").Value).Show
 が実行されているのか確認してみて下さい。
 　
（HANA） 2014/04/04(金) 13:31

コメント返信：

[ 一覧(最新更新順) ]

[[20140328104656]] 『別シートのデータをコピーして貼り付けたい』（ままん） ページの最後に飛ぶ

[[20140328104656]]　『別シートのデータをコピーして貼り付けたい』（ままん）　ページの最後に飛ぶ