Excel problem

[Maureeze]

I have list of events which are dated. More often than once, i have
more events on one day. I want to filter todays events from that list
and show them on a different sheet. Can someone please help me.....

Data looks like this:

09/11/07 IBM 0.47
10/11/07 INTC 0.50
10/11/07 DELL 0.75
11/11/07 GOOG 3.25

If today is 10/11/07, i want to show on a different sheet
10/11/07 INTC 0.50
10/11/07 DELL 0.75

ps: it can be 3 events, but it can also be 15. So you cannot really
say how many events there are going to be.

Thanking you all in advance for looking at this problem!

 

[Bob Phillips]

Look at Advanced Filter in help.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

You need two sheet names old and new. Change these sheet names as required

Sub copy_today()

ShNewRowCount = 1
ShOldRowCount = 1
With Sheets("Old")
Do While .Cells(ShOldRowCount, "A") <> ""
If Date = .Cells(ShOldRowCount, "A") Then
.Rows(ShOldRowCount).Copy Destination:= _
Sheets("New").Rows(ShNewRowCount)
ShNewRowCount = ShNewRowCount + 1
End If

ShOldRowCount = ShOldRowCount + 1
Loop
End With
End Sub

 

[michael.beckinsale]

Hi Maureeze,

You didn't say whether you want to do this using VBA or manuallly but
since you have posted to this group l assume you want to use VBA.

Assuming the data is in culumns A,B,C

the process would be, filter column A, copy the range, paste to
another sheet, so the code would something like this (assuming no
other data is on the sheet)

Sheets("Sheet1").AutoFilter Field:=1, Criteria1:=myDate
Sheets("Sheet1").Cells.Copy Destination:=Sheets("Sheet2").Range("A1")

where the variable myDate is the date you want to filter. This could
be defined by using an input box. If the source sheet contains other
data you can use the .Range("A1:C10".CurrentRegion.Copy instead
of .Cells.Copy

Hope this helps

Regards

Michael Beckinsale

 

[Maureeze]

Hi Maureeze,

You didn't say whether you want to do this using VBA or manuallly but
since you have posted to this group l assume you want to use VBA.

Assuming the data is in culumns A,B,C

the process would be, filter column A, copy the range, paste to
another sheet, so the code would something like this (assuming no
other data is on the sheet)

Sheets("Sheet1").AutoFilter Field:=1, Criteria1:=myDate
Sheets("Sheet1").Cells.Copy Destination:=Sheets("Sheet2").Range("A1")

where the variable myDate is the date you want to filter. This could
be defined by using an input box. If the source sheet contains other
data you can use the .Range("A1:C10".CurrentRegion.Copy instead
of .Cells.Copy

Hope this helps

Regards

Michael Beckinsale

Do you know if it is possible without VBA and without advance
filtering?

 

[Roger Whitehead]

Use MSQuery;

Name your data range, including field names
Goto (insert if necessary) a new sheet (or you could do this from another
WB)
Select: Data, Import External Data, New Database Query
Select 'Excel Files' from the database tab
Select the current file name & folder from the next dialogue
Open the range name (with the + symbol) from the next dialogue, &select the
field names you require, click Next
Enter any conditions, click Next.
Enter Sort criteria, click Next.
You can now test your output in MSQuery, or drop it straight back into your
WS.

Right-clicking the resulting data range will enable you to change the
criteria, making the tool reusable.

 

[Bernd P]

Hello Maureeze,

Suppose your data is in Sheet1, A1:C4.

Then enter into Sheet2, A1:E4:
Date 39397
Occurrence 0
1st =B2+MATCH(Sheet2!$B$1,OFFSET(Sheet1!$A$1:$A$1000,B2,0),)
=INDEX(Sheet1!$1:$65536,Sheet2!$B3,1) =INDEX(Sheet1!$1:$65536,Sheet2!
$B3,2) =INDEX(Sheet1!$1:$65536,Sheet2!$B3,3)
2nd =B3+MATCH(Sheet2!$B$1,OFFSET(Sheet1!$A$1:$A$1000,B3,0),)
=INDEX(Sheet1!$1:$65536,Sheet2!$B4,1) =INDEX(Sheet1!$1:$65536,Sheet2!
$B4,2) =INDEX(Sheet1!$1:$65536,Sheet2!$B4,3)

[39397 is 11-Nov-2007]
Copy B4:E4 down as far as necessary.

Regards,
Bernd