importing data from other spreadheet

[yasarayhanka]

Here is what I am trying to do,
I have 5 cells which represent the weeks of the month that I want to be able to change to the current months weeks like 200731, 200732 ect. (also for the months which have 4 weeks I should be able to leave the last cell blank)
and I want the macro to read the data written in these designated cells and pull the data in another spreadsheet for these weeks. each week has many data. also I need to capture the data at the right side of the week column. and everyday this data changes so macro should be relavent.
I know i need to use case function, but I could not figure it out yet. Any help will be appreciated.

Thanks,
Yasar

[yasarayhanka]

Here is what I am trying to do,
I have 5 cells which represent the weeks of the month that I want to be able to change to the current months weeks like 200731, 200732 ect. (also for the months which have 4 weeks I should be able to leave the last cell blank)
and I want the macro to read the data written in these designated cells and pull the data in another spreadsheet for these weeks. each week has many data. also I need to capture the data at the right side of the week column. and everyday this data changes so macro should be relavent.
I know i need to use case function, but I could not figure it out yet. Any help will be appreciated.

Thanks,
Yasar

Ok I think I figured out some of it but my code will not work,

Windows("kaya.xls").Activate
Range("G1").Select
Range(Selection, Selection.End(xlDown)).Select
Range("G1:G12491").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Workbooks("test.xls").Sheets("Sheet2").Range("A1:A5"), _
Unique:=False


this code does the advance filtering for the 4 weeks but if I have an empty cell it will not work, also the ranges are relative so next week this will not work,
this is so far I could come,
I am using excel 2003 if that is going to help any.

Thanks in advance for the help.
yasar

[mdmackillop]

Can you post your workbook?

[yasarayhanka]

Can you post your workbook?
here is a sample workbook is pretty much the same,
I am trying to get the specified shipped week data to another place (here it would be sheet 3) but the shipped week data will change as the ordersd are shipped.

thanks,
yasar