Data pulled from date

[culverhr]

I am working on creating a production schedule. My main sheet has a date in column B2. If this date is found in another worksheet in say column H, I want it to pull the data from column C to the main spreadsheet. I initially thought a vlookup would work but it is not working out. I only want the data pulled if the date in column H matches the date in the main worksheet column B2. Any ideas???

 

[jim may]

Your Main Sheet Before running Macro "Foo" should look like this:

Excel Workbook
	A	B	C	D	E	F	G	H
1			Header ColC	Header ColD	Header ColE	Header ColF	Header ColG	Header ColH
2		10/15/2011
Main

Excel 2007

Your Sheet2 should look like this:

Excel Workbook
	A	B	C	D	E	F	G	H
1			asd	sdf	dfg	fgh	ghj	hj
2			My 10/15 Stuff	XXX	YYY	ZZZ	XYZ	15-Oct
3								20-Oct
4								25-Oct
5			My 10/15 Stuff	XXX	YYY	ZZZ	XYZ	15-Oct
6								15-Nov
7			My 10/15 Stuff	XXX	YYY	ZZZ	XYZ	15-Oct
8								25-Oct
9								3-Nov
10								15-Nov
Sheet2

Excel 2007

Run this Macro:

Sub Foo()
firstdate = Sheets("Main").Range("B2")
lastdate = Sheets("Main").Range("B2")
Range("C1:H10").Select
Selection.AutoFilter Field:=6, Criteria1:=">=" & _
firstdate, Operator:=xlAnd, _
        Criteria2:="<=" & lastdate
Set Rng = Range("C2:H7").SpecialCells(xlCellTypeVisible)
Rng.Copy Destination:=Sheets("Main").Range("C2")
End Sub

Now your Main Sheet Afterwards should look like this:

Excel Workbook
	A	B	C	D	E	F	G	H
1			Header ColC	Header ColD	Header ColE	Header ColF	Header ColG	Header ColH
2		10/15/2011	My 10/15 Stuff	XXX	YYY	ZZZ	XYZ	15-Oct
3			My 10/15 Stuff	XXX	YYY	ZZZ	XYZ	15-Oct
4			My 10/15 Stuff	XXX	YYY	ZZZ	XYZ	15-Oct
Main

Excel 2007

 

[jim may]

Here's the code - CLEANED-UP ...

Sub Foo()
Application.ScreenUpdating = False
firstdate = Sheets("Main").Range("B2")
lastdate = Sheets("Main").Range("B2")
Sheets("Sheet2").Activate
Range("C1:H10").Select
Selection.AutoFilter Field:=6, Criteria1:=">=" & _
firstdate, Operator:=xlAnd, _
        Criteria2:="<=" & lastdate
Set Rng = Range("C2:H7").SpecialCells(xlCellTypeVisible)
Rng.Copy Destination:=Sheets("Main").Range("C2")
ActiveSheet.ShowAllData
Sheets("Main").Activate
Application.ScreenUpdating = True
End Sub

 

[culverhr]

It has a problem with the Set Rng = Range("C2:H7").SpecialCells(xlCellTypeVisible), it will not accept.

 

[jim may]

Sorry, Try This INSTEAD (Replaced code): Jim

Sub FooX()
Application.ScreenUpdating = False
firstdate = Sheets("Main").Range("B2")
lastdate = Sheets("Main").Range("B2")
Sheets("Sheet2").Activate
Set Rng = Range("C1").CurrentRegion
Rng.AutoFilter Field:=6, Criteria1:=">=" & _
firstdate, Operator:=xlAnd, _
        Criteria2:="<=" & lastdate
Set Rng1 = Rng.Offset(1).Resize(Rng.Rows.Count - 1)
Set Rng1 = Rng1.SpecialCells(xlCellTypeVisible)
'Set Rng1 = Rng.Offset(Rng(1).SpecialCells(xlCellTypeVisible)
Rng1.Copy Destination:=Sheets("Main").Range("C2")
ActiveSheet.ShowAllData
Range("C2").Select
Sheets("Main").Activate
Application.ScreenUpdating = True
End Sub

 

[jim may]

Well?