vlookupで参照するシート名と範囲を変えたい

質問

お世話になります。
vlookup関数について質問です。
新規作成する表の列B2からF2（１行目は項目行）に別のブック
「商品マスタ」からvlookupで必要なデータを検索して入力
B2からFのデータのある最終行までコピーするというマクロを作りました。

Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,[商品マスタ.xlsx]商品マスタ2017.10.5!R2C1:R430C6,COLUMN(RC),0)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:F2"), Type:=xlFillDefault
Range("B2:F2").Select

Range("B2").Select
Selection.AutoFill Destination:=Range("B2:F2"), Type:=xlFillDefault
Range("B2:F2").Select
Selection.AutoFill Destination:=Range("B2:F" & Cells(Rows.Count, "A").End(xlUp).Row)
On Error Resume Next
With Range("B:F").SpecialCells(xlCellTypeFormulas)
.Value = .Value
End With

★商品マスタの内容を更新時に新規シートを追加しているので
《商品マスタ2017.10.5!R2C1:R430C6》この部分のシート名と参照範囲を
毎回手動で書き直していますが、シート名と参照範囲を自動で切り替える
方法がありましたら教えてください。
シートは最新版のデータが入ったものを常に使用します。
よろしくお願いします。

回答No.5

SI299792 · ベストアンサー

２種類作りました。
Macro1が新しい商品マスターが右端にある場合
Macro2がそうとは限らない場合です。
'
Option Explicit
'
Sub Macro1()
'
　　Dim Row As Long
　　Dim MSheet As Worksheet
'
　　Row = Workbooks("商品マスタ.xlsx").Sheets.Count
'
　　Set MSheet = Workbooks("商品マスタ.xlsx").Sheets(Row)
　　Row = MSheet.Cells(Rows.Count, "A").End(xlUp).Row
　　[B2] = "=VLOOKUP($A2,[商品マスタ.xlsx]" & MSheet.Name & "!$A$2:$F$" & Row & ",COLUMN(B2),0)"
　　Row = Cells(Rows.Count, "A").End(xlUp).Row
　　[B2].Copy Range("B2:F" & Row)
　　With Range("B:F")
　　　　.Value = .Value
　　End With
End Sub
'
Sub Macro2()
'
　　Dim Count As Long
　　Dim Row As Long
　　Dim SDate As String
　　Dim Ndate As Date
　　Dim Odate As Date
　　Dim MSheet As Worksheet
'
　　For Each MSheet In Workbooks("商品マスタ.xlsx").Sheets
　　　　Count = Count + 1
　　　　SDate = Mid(MSheet.Name, 6)
　　　　SDate = Replace(SDate, ".", "/")
　　　　Ndate = SDate
　　　　Stop
'
　　　　If Odate < Ndate Then
　　　　　　Row = Count
　　　　　　Odate = Ndate
　　　　End If
　　Next MSheet
'
　　Set MSheet = Workbooks("商品マスタ.xlsx").Sheets(Row)
　　Row = MSheet.Cells(Rows.Count, "A").End(xlUp).Row
　　[B2] = "=VLOOKUP($A2,[商品マスタ.xlsx]" & MSheet.Name & "!$A$2:$F$" & Row & ",COLUMN(B2),0)"
　　Row = Cells(Rows.Count, "A").End(xlUp).Row
　　[B2].Copy Range("B2:F" & Row)
　　With Range("B:F")
　　　　.Value = .Value
　　End With
End Sub

回答No.6

SI299792

既に回答が付いていまたね。確認せずに回答してしまいました。
一応間違いがあったので、書いておきます。
Stopは消して下さい。
何でここは回答を取り消せないんだ（ぼやき）

回答No.4

mt2015

「商品マスタ」ブックの新しいシートが常に一番目とかのルールだったら楽だったんですけどね。
シート名を全部なめて、シート名から「商品マスタ」を除いた残りの文字列が日付になるシート調べ、その日付が一番大きいシートのデータ範囲を調べる。
と、いう内容のFunctionを作ってみました。あなたのコードと同じ標準モジュールに置いてください。

Function Sample() As String
　　Sample = "NODATA"
　　dDate = DateValue("1900-1-1")
　　nSel = 0
　　With Workbooks("商品マスタ.xlsx")
　　　　For i = 1 To .Sheets.Count
　　　　　　sSName = .Worksheets(i).Name
　　　　　　sSName = Replace(Replace(sSName, "商品マスタ", ""), ".", "-")
　　　　　　If CDate(sSName) Then
　　　　　　　　If dDate < DateValue(sSName) Then
　　　　　　　　　　dDate = DateValue(sSName)
　　　　　　　　　　nSel = i
　　　　　　　　End If
　　　　　　End If
　　　　Next i
　　　　If nSel > 0 Then
　　　　　　Sample = .Worksheets(nSel).Name
　　　　　　nRow = .Worksheets(nSel).Range("A2").End(xlDown).Row
　　　　　　Sample = "=VLOOKUP(RC1,[商品マスタ.xlsx]" & Sample & "!R2C1:R" & nRow & "C6,COLUMN(RC),0)"
　　　　End If
　　End With
End Function

で、あなたのコードの一部を以下のように変更します。
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,[商品マスタ.xlsx]商品マスタ2017.10.5!R2C1:R430C6,COLUMN(RC),0)"
↓
sFormula = Sample()
If sFormula = "NODATA" Then Exit Sub
ActiveCell.FormulaR1C1 = sFormula

お礼

新しいシートは一番右に来るようにしています。
まずは書き込んでくださったコードで試してみますね。
分からない部分がありましたらまた質問します。
ありがとうございました。

回答No.3

HohoPapa

>3月1日更新したとすると
という日付は、どこかのシートのどこかに書き込まれているんですか？

>>シート名に書き込まれています
という返事はしないでくださいね。
シート名をVBAで求めるんですから。

それとも一番後方のシートとか、、
2枚目のシートだ！といた決まりがありますか？

お礼

日付はシートの中には書き込まれていません。
更新したシートは一番後方になるようにしています。
説明が足らず分かりにくい質問になってしまいましたので
処理内容について整理し直してみます。
ありがとうございました。

回答No.2

HohoPapa

>これで分かるでしょうか？
いいえ、わかりません。

>《商品マスタ2017.10.5!R2C1:R430C6》この部分のシート名と参照範囲を
>毎回手動で書き直していますが
ここを自動化したいんですよね？

ならば、変化する部分は
《商品マスタYYYY.MM.DD!R2C1:RnnnC6》
と仮定すると

YYYY.MM.DD　と　nnn　この2か所と思います。
この部分を何の情報をもとにVBAで求めればいいのかが
私にはわからないのです。

言い換えれば、質問者さんは、
YYYY.MM.DD　と　nnn　この2か所を
何をもとに（あるいはどこを見て）書き換えているのかということです。

お礼

補足の説明も情報が足らずにすみません。
どういう処理をしたいのか整理してまとめ直してみます。
ありがとうございました。

回答No.1

HohoPapa

>《商品マスタ2017.10.5!R2C1:R430C6》この部分のシート名と参照範囲を
>毎回手動で書き直していますが、シート名と参照範囲を自動で切り替える
>方法がありましたら教えてください

自動で切り替えるとの行、
どのようなルールで切り替えればいいのかがわかりません。

"=VLOOKUP(RC1,[商品マスタ.xlsx]商品マスタ2017.10.5!R2C1:R430C6,COLUMN(RC),0)"

例えば、"2017.10.5"を実行日に置き換える
　とか、
どこかのシートのどこかのセルに日付があり、
その日付に置き換える
　とか
が考えられますがいかがでしょうか。

また、
参照範囲を自動的に切り替えるという要望ですが
これも、どのように切り替えればいいですか？

例えば、
有効なデータが埋まっている範囲を指定することが考えられますが
だとすると、
有効範囲の１列目には、空白セルは無いという理解でいいですか？

言い換えれば、
有効範囲の最終行をどのような条件で判断すればいいのか？
ということです。

補足

補足します。

《商品マスタ》シート名商品マスタxxx(日付）
A 　　　　B 　　　　C 　　　　　D 　　　　 E 　　　　F
1 商品番号　　商品コード　　品名１　　品名２　　　棚No.　　単価
2 　abcd 　 　12345 　りんご　Lサイズ　　　　１　　　　200

《作成する表》
A 　　　　B 　　　　C 　　D 　　　 E 　　F
1 商品番号　商品コード　品名１　品名２　棚No.　単価
2 abcd

このような表の構成になっていてA列にはデータがあります。
商品マスタは更新日付をシート名としていますので、3月1日更新
したとすると商品マスタ2018.3.1という名前のシートを追加します。

シートを追加する毎に新しいシート名のAからFのデータのある
範囲を参照させるようにしたいと思っています。
これで分かるでしょうか？上手く説明出来ずすみません。