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!!!

 

[Niveditha]

I've been trying the record Marco option and it has been awesome but the coding logic of extracting between dates is still tricky for me. Can someone please suggest a go to person/site/source to learn it?

 

[dermie_72]

Sorry Niveditha, I was offline for a few days. Can you post the code you recorded, using the dates you selected. I can then show you how to adjust the code to suit your needs.

 

[Niveditha]

Hey dermie! Thank you for replying. I could share the filter+copycolumn+auto format that I'm now doing using macros. Retaining the text between dates and appending ABC says, that's done manually.

I will share the code first thing I get to work, which is in another 9hours. Thanks much for your patience.

 

[Niveditha]

Sigh! Im in a real fix guys and would appreciate if anyone can get me out of this!

I tried to rerecord my macro with autofilter and now nothing seems to work.

What I have in hand is simply a copy column structure. ( sheet 1 to sheet 2) copies columns after I have applied manual filter

Pending things for which I have no clue:

1) how to automate the autofilter ( I can possibly skip this)
2)What I REALLY REALLY want to know: How to append ABC says :'(

 

[dermie_72]

Here is some code the used autofilters. You can set variable to the sections. The best way to find the format would be to record a macro, set filters and choose the dates you want. Then remove the date component from the code and insert the variable.
For example.

Sub autofilter_column_L()
Dim lr As Long
lr = ActiveSheet.UsedRange.Rows.count 
    With Range("L1:L" & lr)
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:= "<3/02/2015", Operator:=xlAnd        'if you want it to equal the date or be greater than then change the math sign.
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete         'do not enter this line if you do not want to delete the visible data.
    End With
End Sub

remove the 3/02/2015 from the code and put " & filterdate & " instead
you need to define the filterdate variable and assign a value as well.

enter DIM filterdate as date just below Dim lr as long
filterdate can be a value in a cell or it can be from an input box it depends on how you want to assign the value to it.
you could enter a value in cell ZZ1 and then use filtervalue = range("ZZ1").value
or you could use the inputbox
filtervalue = inputbox("Enter the filter date", vbok)

hope that helps.