Excel VBAを使って他ブックのデータ抽出とコピー

質問

VBA初心者です、よろしくお願い致します。

現在、Excel97を使用して、実績をまとめるファイルを作成しています。
このファイルのシートに、他ブック（ネットワーク上固定フォルダ固定ファイル名）のデータを抽出コピーを行いたいと思っています。
他ブックは、品名（A3）、日付（C3～AG3）（1日～31日）という列からなり、品名数の増減があります。

様々な情報を参照させていただき下記のようなコードを作成しました。
Set wb1 = Application.ActiveWorkbook
'ブックを開く
Application.ScreenUpdating = False
Set wb2 = Application.Workbooks.Open(dataDirJisseki & dataNameJisseki, ReadOnly:=True)
'コピーする
With wb2
.Worksheets("ＳＭＴ生産実績").Range("A3:A65536").Copy _
Destination:=wb1.Worksheets("work_jisseki").Range("A1")
.Worksheets("ＳＭＴ生産実績").Range(colTmp & "3:" & colTmp & "65535").Copy _
Destination:=wb1.Worksheets("work_jisseki").Range("B1")
End With
'ブックを閉じる
Set wb1 = Nothing
wb2.Close Savechanges:=False
Set wb2 = Nothing

Application.ScreenUpdating = True
上記プログラムでも希望した動作にはなるのですが、下記について改善したくご相談させていただきました。
(1)抽出する列を他ブックから検索したい
　現状では、抽出する列を自ブックのシートの"日付"セルに入力させ、その値に該当する他ブックの列名をSelect Caseを使って判断しています。
Select Case Day(Range("日付"))
Case "1"
colTmp = "C"
　　　　：
Case "31"
colTmp = "AG"
End Select
　これでは他ブックの列構成が変わってしまうと異なるデータ列を取ることになるため、日付を使って他ブックから該当列を検索するようにしたいです。
(2)他ブックのデータ数（行列数）を使ってコピーをしたい
　現状では行数最大値の65536でコピーしていますが、他ブックのデータ数でコピーしたい。
以上ですが、是非お力添えをいただければと思います。
説明等不足がありましたら、そちらも指摘ください。よろしくお願いします。

回答No.3

onlyrom · ベストアンサー

＞他ブックは、品名（A3）、日付（C3～AG3）（1日～31日）

この1日～31日は、1日、2日、、、31日　と"日"が付いてるものとする
また、極力質問者のコードを利用。

'------------------------------------
Sub test()
　Dim wb1 As Workbook
　Dim wb2 As Workbook

　Dim myDay
　Dim Clm As Integer
　Dim LastRow As Long

Application.ScreenUpdating = False

　Set wb1 = ActiveWorkbook
　Set wb2 = Workbooks.Open(dataDirJisseki & dataNameJisseki, ReadOnly:=True)

　myDay = Day(wb1.Worksheets("work_jisseki").Range("日付").Value) & "日"

With wb2.Worksheets("ＳＭＴ生産実績")
　　LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
　　For Clm = 2 To .Cells(3, .Columns.Count).End(xlToLeft).Column
　　　　If .Cells(3, Clm).Value = myDay Then
　　　　　　.Range("A3:A" & LastRow).Copy _
　　　　　　　　wb1.Worksheets("work_jisseki").Range("A1")
　　　　　　.Range(.Cells(3, Clm), .Cells(LastRow, Clm)).Copy _
　　　　　　　　wb1.Worksheets("work_jisseki").Range("B1")
　　　　　　Exit For
　　　　End If
　Next Clm
End With

　wb2.Close False

Application.ScreenUpdating = True
End Sub
'----------------------------------------

検索範囲が広い場合の検索には、ふつうFIND関数などを使いますが
今回は検索範囲が狭いので（３行目の該当列まで）For～Nextを使用
それと列構成が変るといっても少なくとも１列目は品名固定ですよね
（それは質問の文言及び提示のコードより判断）
以上。

お礼

onlyrom様
ありがとうございます。期待通りの動作ができました。
また何かありましたらご指導よろしくお願い致します。

回答No.2

n-jun

＃１です。

（１）
１日がＣ列だとしたら、

Dim niti As Integer
niti = 16 '日付

MsgBox "セルの選択"
Cells(3, niti + 2).Select
MsgBox "列の選択"
Columns(niti + 2).Select

でセルや列を選択できます。
（シートの指定は省略してます）

補足

n-jun様
回答ありがとうございます。アドバイス内容ですと、確かに列、セルの選択はできますが、これでは1日がC列固定になり、希望する選択と異なります。
他ブックの行位置固定の見出し部分（品名、日付）から本日の日付を検索し、該当する日付のセル全体（範囲終了は品名列のデータ最後）をコピーして、自ブックにペーストしたいのです。

回答No.1

n-jun

（１）他ブックに対してどのような条件をどこから与えると、他ブックのどのシートに対して
どのように検索したいのかが不明。

日付を基に列を検索する場合に、ブックによって検索開始位置に変動があるのかないのか。
それはどこになるのか。（１行目とか３行目のデータがある範囲とか）

（２）
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_cell.html#currentregion
・アクティブセル領域の選択(CurrentRegionプロパティ)
・使用済みの最終セルの選択(UsedRangeプロパティ)
などを他ブックに対して実行してみては。

補足

n-jun様
説明が足りず、ご迷惑をおかけして申し訳ありません。
下記に補足させてください。
(1)検索対象は他のブックのsheet1です。Sheet1の3行目の列に対して検索をさせます。この検索位置は変動はありません。
ちなみにこの他ブックはブック保護がかかっています。
(2)リンク指示ありがとうございます。早速試してみますが、また分からないことがありましたら、質問させていただきます。