シートの内容コピーマクロについて

質問

エクセルシートに作成したシートのデーターを、別シートに自動で反映させてたいと思っております。
該当する文字を入力するだけでマクロを使用してその下にある数字を全てコピーできるようしたいと思っております。

添付シートを参照して頂ければと思いますが、シート1の元データーから、別シート2に文字を返して、その下にある文字が自動で反映できるような仕組みを構築したいと考えております。

添付は抜粋で元データーは400件以上あり、シート1の基本となる大元は、縦軸が800セル　
横軸（時間軸）で、305セルまで使用しており、そこに順不同で品目と、下の数値が違うデータが入力させれております。

そこで、品目だけ入力すればその下の数字が自動で反映出来ればと思っております。
下記マクロですと、シート1の元データーがシート2へ全てが反映させれません。
何処を変更すればよろしいのでしょうか？

-----------------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim cnt As Long, c As Range, r As Range, myRng As Range, wS As Worksheet
Set wS = Worksheets("Sheet1")
If Target.Count = 1 Then
If Target <> "" Then
Set c = wS.Cells.Find(what:=Target, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Set myRng = c.CurrentRegion
For Each r In myRng
cnt = cnt + 1
If r = c Then Exit For
Next r
If Target.Column - cnt + 1 > 0 Then
myRng.Copy Target.Offset(, -cnt + 1)
Else
MsgBox "左側列数が不足です。"
Exit Sub
End If
Else
MsgBox "該当データなし"
With Target
.Select
.Value = ""
End With
Exit Sub
End If
End If
End If
End Sub 'この行まで
----------------------------------------------------------------------------------------------------

トラックの搬入時間管理の為、タイムリーな処理が必要な為、お詳しい方のお知恵をお借りできればと思いいます。　お忙しい所申し訳ありませんが、どなたかお力添えをよろしくお願いいたします。

回答No.7

imogasi

#1です。
勘違いだったらすみません。
Set myRng = c.CurrentRegion
For Each r In myRng
cnt = cnt + 1
If r = c Then Exit For
Next r
の部分は、ｃｎｔは、CurrentRegionの「セル」数を数えていますよね。
＞myRng.Copy Target.Offset(, -cnt + 1)に、（それもＯｆｆｓｅｔの「列の部分」に）cntが使われているのはなぜ？。どういう意味ですか？。
ーー
私の確認テストは、下記です。
Range("B3:D6")に勝手なデータを入れて
Sub test01()
Set myrng = Range("B3").CurrentRegion
For Each r In myrng
cnt = cnt + 1
Next r
’ここまでは質問のコード
MsgBox cnt
MsgBox myrng.Count
MsgBox myrng.Columns.Count
End Sub
を実行したら
cntは１２、 myrng.Countは１２、myrng.Columns.Count(CurrentRegionの列数）は３になりました。
このmyrng.Columns.Countを使うべきではないのでしょうか？
For Each r In myrng
cnt = cnt + 1
Next r
の部分はmyrng.Countで済むのでは。

回答No.6

imogasi

#1です。補足していただいて、ありがとうございました。
しかしよくわかりませんでした。
ーー
Ｓｈｅｅｔ１
りんご　　Ｋ５セル。こういうＫ５セルと表現するほうがわかりやすい。Ａ１形式。
　　　　セル番地の表現に、Ｒ１ＣI方式があるのは知ってますが、あまり使われない。この質問には両者（Ａ１形式とＲ１ＣI方式的表現）の表現が混じっていてわかりにくいと思う。
　　　　　エクセルやＶＢＡの解説書でもあまり見かけないでしょう。
　　　　　これは場合によって、変わるので、人間が、シートのセルをＳｅｌｅｃｔｉｏｎしするのですね。
行範囲について
１＋５＝６行から５＋１３＝１８行にわたってデータがあるのですね。
列範囲について
－１５とは？Ｋ列は第１１列目なので左へー１５とできないのでは？
色分け
「値」の取得や代入では不十分なのですね。
Ｓｈｅｅｔ２
Ｂ２３が貼り付けの起点（左上隅セル）ですね？＜－－「横Ｂ、縦２３のセルに」から
＞りんごと記入したら
りんごと記入して？セルの値に「りんご」をコピー貼り付けして？
＞縦Ｂ、横２３
これは毎回（りんご以外の時も）、固定位置ですか？。
＞その下の
その下のＢ２４を起点にして、その下部（右方向＋下方向に）にのこと？
ーー
以上は参考にしてください。回答には至れないのですみません。

回答No.5

imogasi

#Iです。質問と画像を、もう一度見直してみると
「りんご」は実際はトラック番号や運転手名か。
その下の１が並んでいるセル範囲のデータは（参考までに）どんなデータなのか。
もし書いてあれば、読者が類推できるかも。
この塊のデータ（凸凹のセル範囲）をどう見分けるのか？スペースが連続データの最右や最下のセルにあれば、情報（りんごのデータ）の境界ということか。
ーー
セル範囲は、たとえば
Sub test01()
Range("B4").CurrentRegion.Select
End Sub
（ＣＴＲＬ＋ＳＨＩＦＴ＋＊の操作に当たる）
を実行すると、Ｂ４：Ｆ７（私のテスト例）の長方形のセル範囲を選んでしまって、凸凹はお構いなしである。
これをＳｈｅｅｔ２（１？）の所定のセル範囲に張り付けては（スペースのセルが混じる）ダメなのか？
そこから空白セルを、一発の操作で（ＶＢＡでもコードを書ける）すべて選択できるが
それでは役立たないのかな。
ーー
CurrentRegion.Select
の各列、各行で下からＥｎｄ（ｘｌUp）右からＥｎｄ（ｘｌＴｏＬｅｆｔ）で終わりのセル位置を調べないといけないと思う。
例で「りんご」以外の関連データが１スペースでも開けて迫ってきているのか。
こういうことを説明しないと、質問の趣旨が伝わらないのではないか。
会社の業務の秘密を出したくないとしても限度かある。うまく他の例にでも置き換えて説明すること。この頃こういうのが多くて質問の意味がわかりにくい。読者の立場にもなってください。

回答No.4

kkkkkm

N03　訂正

myRng.CurrentRegion.Select
↓
myRng.Select

でした。

回答No.3

kkkkkm

No2です。

For Each r In myRngの部分が列数を求めてるのでしたら

myRng.Columns.Count

で取得できます。

また、左上の行と列は

myRng.Cells(1).Row
myRng.Cells(1).Column

で取得できます。

コピーしたい範囲が適切に選択されているかを調べてみたいのでしたら
myRng.CurrentRegion.Select
を入れてそこでで止めてみたらいかがでしょう

回答No.2

kkkkkm

画像ではデータが一塊のように見えるので、単純にCurrentRegion部分をコピーするだけではだめなんですか？

回答No.1

imogasi

質問の内容がよく伝わらない。
補足希望。
ーー
大きな処理は＞トラックの搬入時間管理の為、らしいが画像のシート例の内容が抽象的すぎて、どこが注目点かよくわからない。
わたしの持論では、画像というのは、うまい説明者が説明しないと、趣旨はよく伝わらないと思う。画像が見えているだろうから、わかるはず、というのはうそだと思う。
もっと、「処理してほしい内容」を、「言葉」で表現できる訓練をしてほしい。
人間が紙と鉛筆でやる場合のことを念頭に作業を、１歩１歩文章化してみたらうまく説明できると思う。
例
・「りんご」に当たるものをたとえばＢ１セルに入力。
・「りんご」をどのシートの（多分どの行？列を対象か）のどの範囲を検索し、
見つかった場合、どの範囲のデータを、どのシートのセル範囲にもってくるのか
コピー貼り付け（値のみ）でよいのか。
（それは「別シート」のどの行、列やセル範囲に持ってくるのか。）
・「りんご」は探索範囲に、複数出現しないのか。
ーーー
ＶＢＡコードを載せたから、聞きたいことが判るというのも嘘。初心者が自分流に書いている場合が多いのと、回答者が読み解くには時間がかかる。
自分のＶＢＡコードは、ないもの（参考までに添付はしても）として質問したほうがよい。
ーー
どの点がうまく行かないのか、エラーになるのか、を詳説するのも必要。
この質問全体が丸投げ的で、小生の経験から、問題点をよく考え抜いたら、壁（行き詰まった点）は「１センテンスに」表現できる場合が多い。

補足

大変失礼をいたしました。始めてでしたので、とても参考になりましたありがとうございます。
補足説明させて頂きます。

sheet1 の、エクセルシート上にランダムに”りんご”や”いちご”などの品名が記載されております。仮にりんごが横　”ｋ”　縦”5”の所に記載されており、その下に縦+1～+13段　横-15～+3　のセルに”1”と数値が記入されております。その”1”の文字も色分けがされております。
そのシート全体が元となり、sheet2 の　横　”Ｂ”　縦”23”のセルに”りんご”と記入したら
その下の”1”と色がそのまま反映されるようなマクロを組みたいのですが、どのようにすればよろしいのでしょうか。よろしくお願いいたします。