Extract data from one range and place in the same sheet

[tkraju via OfficeKB.com]

Col A-------------------Col B------------------ColC---------------------------
Col D-----------------------ColE
Name-------------------Date-------------------Amount----------------------"
Mary"-------------------- Apr-09
John------------------01-Apr-09--------------$100 ------------------------
01-Apr-09---------------$125
Mary------------------01-Apr-09--------------$125-----------------------------
02-Apr-09---------------$567
Raj--------------------02-Apr-09---------------$50----------------------------
--04-Apr-09---------------$321
Mary------------------02-Apr-09---------------$567
John-------------------03-Apr-09---------------$213
Bill--------------------03-Apr-09----------------$456
Mary-----------------04-Apr-09-------------------$321
Cathy----------------06-Apr-09---------------$310
Mary------------------01-May-09--------------$78
Raj---------------------02-May-09--------------$567

D1 has dropdown list of Names,and E1 has drop down list of Mon-YY.
I need a macro code to get Mary's Apr-09 data from the used range A1:C to
D2:E32.
If I select Mary(cell D1) and Apr-099Cell E1) ,the output should display like
above.

 

[Simon Lloyd]

tkraju, your data hasn't travelled well it seems, do you just want to
retrieve data from columns D:E32 for every instance of Apr-09?, could
you explain a little more clearly?

Col A-------------------Col
B------------------ColC---------------------------
Col D-----------------------ColE
Name-------------------Date-------------------Amount----------------------"
Mary"-------------------- Apr-09
John------------------01-Apr-09--------------$100
------------------------
01-Apr-09---------------$125
Mary------------------01-Apr-09--------------$125-----------------------------
02-Apr-09---------------$567
Raj--------------------02-Apr-09---------------$50----------------------------
--04-Apr-09---------------$321
Mary------------------02-Apr-09---------------$567
John-------------------03-Apr-09---------------$213
Bill--------------------03-Apr-09----------------$456
Mary-----------------04-Apr-09-------------------$321
Cathy----------------06-Apr-09---------------$310
Mary------------------01-May-09--------------$78
Raj---------------------02-May-09--------------$567

D1 has dropdown list of Names,and E1 has drop down list of Mon-YY.
I need a macro code to get Mary's Apr-09 data from the used range A1:C
to
D2:E32.
If I select Mary(cell D1) and Apr-099Cell E1) ,the output should
display like
above.

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[tkraju via OfficeKB.com]

Hi Simon,
Col A to Col c ,the database is updated daily thus the sales figures in Col c
are added every time date wise( col b) and sales man wise(Col a).I want to
create a report salesman wise ,datewise how much sales done by salesman in a
month.If i put salesman name in D1 and enter Jan-2009 in E1,the detailed
datewise figures of that salesman for the month of Jan-09 be displayed from
D2:E32.

tkraju, your data hasn't travelled well it seems, do you just want to
retrieve data from columns D:E32 for every instance of Apr-09?, could
you explain a little more clearly?

Col A-------------------Col
B------------------ColC---------------------------

[quoted text clipped - 28 lines]

'Excel - programming Excel using VBA or XLM macros'
(http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200904/1)

 

[Per Jessen]

Hi

Try this:

Sub extractData()
Dim sName As String
Dim dDate As Date
Dim Ammount As Double
Dim LastRow As Long
Dim off As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row
sName = Range("D1").Value
dDate = Range("E1").Value
For r = 2 To LastRow
If Format(Cells(r, 2), "mm-yy") = Format(dDate, "mm-yy") Then
If Cells(r, 1).Value = sName Then
Cells(r, 2).Resize(1, 2).Copy Cells(2 + off, "D")
off = off + 1
End If
End If
Next
End Sub

Regards,
Per

Hi Simon,
Col A to Col c ,the database is updated daily thus the sales figures in Col c
are added every time date wise( col b) and sales man wise(Col a).I want  to
create a report salesman wise ,datewise how much sales done by salesman in a
month.If i put salesman name in D1 and enter Jan-2009 in E1,the detailed
datewise figures of that salesman for the month of Jan-09 be displayed from
D2:E32.

tkraju, your data hasn't travelled well it seems, do you just want to
retrieve data from columns D:E32 for every instance of Apr-09?, could
you explain a little more clearly?

Col A-------------------Col
B------------------ColC---------------------------

[quoted text clipped - 28 lines]

'Excel - programming Excel using VBA or XLM macros'
(http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200904/1)

 

[tkraju via OfficeKB.com]

Thank You Per Jessen, It gave me perfect results.
I am curious to learn ,what activity happens line by line when this sub runs?
Can you please explain me?I am just new to vba.

Hi

Try this:

Sub extractData()
Dim sName As String
Dim dDate As Date
Dim Ammount As Double
Dim LastRow As Long
Dim off As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row
sName = Range("D1").Value
dDate = Range("E1").Value
For r = 2 To LastRow
If Format(Cells(r, 2), "mm-yy") = Format(dDate, "mm-yy") Then
If Cells(r, 1).Value = sName Then
Cells(r, 2).Resize(1, 2).Copy Cells(2 + off, "D")
off = off + 1
End If
End If
Next
End Sub

Regards,
Per

Hi Simon,
Col A to Col c ,the database is updated daily thus the sales figures in Col c

[quoted text clipped - 17 lines]

 

[tkraju via OfficeKB.com]

Thank you Per,
If I want data summary(for all salesmen), salesman wise total sales done in a
particular month,how this code will be?
if I enter Jan-09 in cell E1
the output will look like
-----------------------------------
john------Jan-09-----------$678
mary-----jan-09------------$457
Bill--------Jan-09------------$908
thanks

Hi

Try this:

Sub extractData()
Dim sName As String
Dim dDate As Date
Dim Ammount As Double
Dim LastRow As Long
Dim off As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row
sName = Range("D1").Value
dDate = Range("E1").Value
For r = 2 To LastRow
If Format(Cells(r, 2), "mm-yy") = Format(dDate, "mm-yy") Then
If Cells(r, 1).Value = sName Then
Cells(r, 2).Resize(1, 2).Copy Cells(2 + off, "D")
off = off + 1
End If
End If
Next
End Sub

Regards,
Per

Hi Simon,
Col A to Col c ,the database is updated daily thus the sales figures in Col c

[quoted text clipped - 17 lines]