条件のあったシートへデータを転記するマクロ

質問

よろしくお願いします。

ブック内にシート名でマスターシートと在庫日報入力シートの2つがあります。在庫日報シートのA1に日付、A列3行目以降に商品コード、B列3行目以降に各商品名、F列3行目以降に各商品の在庫数量が入っており、毎日更新されます。マスターシートには縦A列3行目以降に日付が入っており、また横1行目（A1,B1,C1....）に各商品名が百以上記載されています。今まで、以下のマクロで在庫日報入力シートの在庫数量をマスターシートの対応するセルに転記していました。（縦の日付を検索し、横の商品名を検索し対応する場所に在庫数量を転記）

Private Sub CommandButton1_Click()
Application.DisplayStatusBar = True
Dim LastR, idxR As Long, trgR, trgC
If MsgBox("日付は正しいですか", vbQuestion + vbOKCancel) = vbOK Then
With Worksheets("在庫日報入力")

LastR = .Range("A65536").End(xlUp).Row
trgR = Application.Match(.Cells(1, 1), Worksheets("マスター").Range("A:A"), 0)
For idxR = LastR To 3 Step -1
trgC = Application.Match(.Cells(idxR, 1), Worksheets("マスター").Range("1:1"), 0)
If IsNumeric(trgR) And IsNumeric(trgC) Then

Worksheets("マスター").Cells(trgR, trgC + 1) = .Cells(idxR, 6)

Else
.Cells(idxR, 1).Interior.ColorIndex = 3
End If
Application.StatusBar = "マスターシートに転記中・・・進行状況 " & idxR & ""
Next idxR

End With
Application.StatusBar = False
MsgBox "終了しました。(処理件数=" & LastR- 3 & "件)", vbOKOnly: Exit Sub
End If
End Sub

今までこれで良かったのですが、今度、マスターシートを削除して、各商品名毎にシートを作成します。そのため、それぞれの商品名シートに在庫日報シートのデータを転記するように変えたいのです。商品名シートはそれぞれA列3行目以降に日付が、となりのB列に在庫数が入るようになっています。
在庫日報シートの各商品に対応した商品名シートを見つけて、そのA列から在庫日報と同じ日付を見つけて、その行のB列に在庫日報シートの在庫数量を転記する。というものです。商品名シートは百以上あり名前は文字列です。

今までのマクロは教えてgooで教えていただきながら作りました。すいませんが、またご教授をお願いします。

回答No.4

onlyrom · ベストアンサー

数百もある商品シートに日付が予め入力されているのですか？
質問からはそう受け取れますが。
で、それを踏まえてのサンプルです。

日付間違い、未入力などのチェックは省略。
CommandButtonは、日報シート上にあるものとする。
該当なし（シート名や日付）の場合は赤色塗りつぶし。

'-------------------------------------------
Private Sub CommandButton1_Click()
　Dim R As Long
　Dim myCell As Range

　On Error Resume Next

　For R = 3 To Cells(Rows.Count, "B").End(xlUp).Row
　　Set myCell = Sheets(Cells(R, "B").Value).Range("A:A").Find(Range("A1").Value, , xlValues, xlWhole)
　　　　If Err.Number > 0 Or myCell Is Nothing Then
　　　　　　Cells(R, "A").Resize(, 3).Interior.ColorIndex = 3
　　　　　　Err.Clear
　　　　Else
　　　　　　myCell.Offset(, 1).Value = Cells(R, "C").Value
　　　　　　Cells(R, "A").Resize(, 3).Interior.ColorIndex = xlNone
　　　　End If
　Next R
End Sub
'---------------------------------------------------------

お礼

お返事ありがとうございました。
提示いただいたコードは参考にさせていただいて、以下のコードにて問題解決いたしました。ありがとうございました。
Private Sub CommandButton1_Click()
Dim n As Long
Worksheets("在庫日報入力").Activate
For n = 3 To Cells(Rows.Count, 2).End(xlUp).Row
With Worksheets(CStr(Cells(n, 2).Value))
trgR = Application.Match(Worksheets("在庫日報入力").Cells(1, 1), .Range("A:A"), 0)
.Cells(trgR, 2).Value = Cells(n, 6).Value
End With
Next n
End Sub

回答No.3

n-jun

基本的には、Worksheets("マスター")と固定されていたものを、
変数として与える事から試してみては？
例えばの例題。
Sub　test()
　Dim　ws　As　Worksheet
　Dim　r　As　Range

　With　Worksheets("在庫日報入力")
　　　　　　　　　　　　　　　　　　　　'仮にセルB3～B5の商品名を使用
　　　　　　For　Each　r　In　.Range(.[B3],　.[B5])　'.Cells(Rows.Count,　3).End(xlUp))
　　　　　　　　　　Set　ws　=　Worksheets(r.Value)
　　　　　　　　　　'　変数wsにシート名（商品名）をセットし、そのシートをアクティブにする
　　　　　　　　　　ws.Activate
　　　　　　　　　　MsgBox　"次へ"
　　　　　　Next
　End　With
End　Sub
基礎は勉強した方が後々楽ですよ。
（私は怠けてますから、理屈がわからず結果だけになってますけど）

お礼

No4を参考にして以下のコードにて解決いたしました。ありがとうございました。
Worksheets("在庫日報入力").Activate
For n = 3 To Cells(Rows.Count, 2).End(xlUp).Row
With Worksheets(CStr(Cells(n, 2).Value))
trgR = Application.Match(Worksheets("在庫日報入力").Cells(1, 1), .Range("A:A"), 0)
.Cells(trgR, 2).Value = Cells(n, 6).Value
End With
Next n
End Sub

基礎はこれから勉強したいと思います。

回答No.2

imogasi

質問が長いし、コードなども読者に読ませているが、したいことはガラット変わったのだから、質問に書く必要ないのでは。動くコードが書けて誇らしかったのだろうが。今後のことだけ書けばよい。
ーー
入力シートに商品名があって、データが収まるべき商品シートのシート名と一致しているなら、
s=商品名文字列
Worksheets(s）.Range(・・・）＝・・へ指定すればよいのでは
文字列的にシート名と入力商品名が、違っているなら、対応表を噛ましてプログラムでシート名を割り出すより他ない。
＞今までのマクロは教えてgooで教えていただきながら
単発でその都度コードを回答者にいただいていても進歩しない。
基礎的なVBAのことを、この際勉強すべきです。

お礼

＞したいことはガラット変わったのだから、質問に書く必要ないのでは。動くコードが書けて誇らしかったのだろうが。今後のことだけ書けばよい。

以前質問したときに今までのコードを提示しないのは説明不足だと言われたので書いてしまいました。こちらとしては、説明不足もコードを見れば説明の補足になるのではと思った次第です。また、このコードを手直しすれば、という安直な考えでした。すいません。

回答No.1

n-jun

在庫日報シートとWorksheets("在庫日報入力")は同じシートですか？

１日ごとに日報シートに記入したものを、商品名シートに転記し、
日報シートはクリアするようなイメージを持ってますが、どうでしょうか？
その場合なら、日報シートには同じ商品名が複数回出てくるのでしょうか？
それとも、１回or０回になりますか？

補足

返事ありがとうございます。

＞在庫日報シートとWorksheets("在庫日報入力")は同じシートですか？
→同じシートです。入力文字が抜けていました

＞１日ごとに日報シートに記入したものを、商品名シートに転記し、
日報シートはクリアするようなイメージを持ってますが、どうでしょうか？
→その通りです。

＞その場合なら、日報シートには同じ商品名が複数回出てくるのでしょうか？それとも、１回or０回になりますか？
→商品名は一回/日です。百数種類商品がありますが、在庫がゼロのものは商品名も在庫数量も在庫日報入力シートには載りません。

説明不足でした。