Match a date to pick up data from another workbook

[girapas]

That's correct:
I've a book per month (say JAN2012, FEB2012 etc.). Each book has sheets, one for every workday (say day1, day2, day3 etc.) where I put data as follows:

book: JAN2012

Book1
	A	B	C	D
1	date:	2/1/2012
2		X1	X2	X3
3	AAA	3296	2899	9803
4	BBB	7534	1991	3895
day1

Book1
	A	B	C	D
1	date:	3/1/2012
2		X1	X2	X3
3	AAA	6541	3403	2126
4	BBB	5996	1895	8919
day2

Book1
	A	B	C	D
1	date:	4/1/2012
2		X1	X2	X3
3	AAA	5754	4729	1230
4	BBB	5388	4798	6289
day3

In another book (book2) I've a sheet named 'database':

book2

Book1
	A	B	C	D	E	F	G
1		----	AAA	----	----	BBB	----
2	date	X1	X2	X3	X1	X2	X3
3	2/1/2012
4	3/1/2012
5	4/1/2012
6	5/1/2012
7	9/1/2012
8	10/1/2012
9	11/1/2012
10	12/1/2012
11	etc.
database

How to run a macro in book2, to get the next date from sheet 'database', column A, look up for that in the book with the same month-year (JAN2012, FEB2012 etc.), in sheet with the same date, pick up the values for AAA/X1,X2,X3 and BBB/X1,X2,X3 and put them in corresponding cells of 'database'?

 

[shg]

Why not put all the data on one sheet, with a column for date? That would make Excel very happy.

 

[girapas]

Because every workday data are put from a user in a specific form, and then he send me by email an Excel file. Periodically I update the database.

 

[JLGWhiz]

I think this is what you want. Try it out.

Run this code from the current month Workbook, with the current day (or last day) sheet as the active sheet. It should look at book 2 column A to find the date of the active sheet as entered in cell B1 and then get the next date from column A of the database sheet.

Code:
Sub glrapas()
Dim sh As Worksheet, sh2 As Worksheet, wb2 As Workbook
Set sh = ThisWorkbook.ActiveSheet
Set wb2 = Workbooks("Book2") 'Edit Workbook name
Set sh2 = wb2.Sheets("database") 'Edit Sheet name
Set dt = sh2.Range("A:A").Find(sh.Range("B2").Value, LookIn:=xlValues)
If Not dt Is Nothing Then
Set nSh = ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))
nSh.Range("B1") = dt.Offset(1, 0).Value
End If
End Sub
Code:

 

[girapas]

Thanks for the reply. When run this line of the code: Set wb2=Workbooks("Book2.xlsx") I get a runtime error - subscript out of range.
Also, I think that in line Set dt = sh2.Range("A:A").Find(sh.Range("B2").Value, LookIn:=xlValues) "B2" must be "B1".

 

[JLGWhiz]

Thanks for the reply. When run this line of the code: Set wb2=Workbooks("Book2.xlsx") I get a runtime error - subscript out of range.
Also, I think that in line Set dt = sh2.Range("A:A").Find(sh.Range("B2").Value, LookIn:=xlValues) "B2" must be "B1".

Yep, the B2 is a typo, it should be B1. Ther workbook Book2 has to be open when you run that macro. I did not include a workbooks.Open line in the code. You can add that in if you know how. As long as it is in the same directory as your Month workbook, you won't have to worry about the path, etc. Just add a line before the Find statement:

Workbooks.Open "Book2.xlsx" .

Then I think I would add before the End Sub:

Workbooks("Book2").Close False