VBA Code to extract data between dates.

[Niveditha]

Hi All,

This is the requirement I have and it would be really helpful if someone can help me here.

1) Sheet1 in excel has 13 columns. I need to extract columns B,J and L to sheet 2.
2) Column B can be copied directly. But, column J is sort of like a history update and the matter would look somewhat like this:
2/8- blah1 blah1 blah1 blah1 2/7-blah blah2 blah2 2/6- blah3 blah3

3)I would need only the latest date and the text that follows it to be copied to next sheet. I also need to append something like "ABC says:-" after the date.

ie., in my sheet 2, I would have column B as 2/8- ABC says:- blah1 blah1 blah1 blah1

I'm a Beginner in macros and excel and any help would be greatly appreciated!!!

 

[dermie_72]

when you say you need to extract column B,J and L to sheet 2, do they need to be in the same column, or in A,B and C?

 

[Niveditha]

Thanks for responding dermie!

Its copied from B, J and L in sheet 1 to to A,B,C in sheet 2.

Should have made that clear earlier. My bad.

 

[dermie_72]

the following code will copy the columns across.

Sub niveditha()
Sheets("Sheet2").Range("B:B,J:J,L:L").Copy Destination:=Sheets("Sheet1").Range("A1")
End Sub

what determines the dates that you will need to capture. Is the blah blah blah's you mentioned dates or text? Does the same apply for column C (the old column L)?

 

[Niveditha]

Hi dermie,

Thank you for helping out

To provide a broader picture,

Sheet 1 --> Column K has a date field . I initially do a manual filter on this field and check only the latest date. So now I'm left with lesser number of rows.
Next I need to copy all data from columns B,J and L to sheet 2 ( columns A, B,C)
B and L in sheet 1 can be copied directly to A and C respectively in sheet 2.

Column J in sheet 1-->
2/8- My name is Niveditha. Im new to excel. 2/7- I said on 2/8 that I'm curious to learn macros. 2/6- Anything can come as a content here.

Column C in sheet 2-->
2/8- ABC says-My name is Niveditha. Im new to excel.

 

[Niveditha]

Just to add on, basically I require only the latest update and not the history updates/previous conversations.
The filter I would have applied initially in this example on column K would be 2/8.

 

[dermie_72]

I initially do a manual filter on this field and check only the latest date.

If you were to run the report today, what dates would you select?

Coding the autofilter with a date variant shouldn't be too hard, just need to know what dates are selected and why

 

[Niveditha]

If you were to run the report today, what dates would you select?

Coding the autofilter with a date variant shouldn't be too hard, just need to know what dates are selected and why

I would be selecting the max date from column K. Ideally, when I open the excel on 10th, the max date in column K would be 9. So I would chose Feb 9 and then do the remaining copy column operations..

 

[Niveditha]

basically, I intend to select the latest date from column K. It does have timestamp appended to it. So I would want all entries for that particular date, irrespective of timestamp. ( 2/9/2016 2:30 PM and 2/9/2016 5:30 PM are both needed )

 

[Niveditha]

Hoping if someone can help me out here
Please do let me know if I should provide anymore details!

Thanks in advance!!!