copy variable range

[phillypdh]

i have data with four columns of text however each day the length the data varies. i need to search a date field for a specific day (col A). when the date is found i need to copy columns B-D for each record with that date dynamically. then paste to another worksheet. if anyone can help me get started i would apprecialte it.

 

[jbeaucaire]

This is what AutoFilter is for.

1) Turn on the macro recorder
2) Click the headers in row1 of your data
3) Turn on the Data > Filter > AutoFilter
4) Use the drop down in column A to filter for the date you want
5) Copy all remaining visible rows to another sheet
6) Turn off the Autfilter and stop the macro recorder


You should be able to edit that resulting code to be dynamic and reusable. Post that code here and we can help with that.

 

[phillypdh]

unfortunately there is too much data to list all the dates when i use auto filter.

 

[HalfAce]

But... if you can record your actions for just one of the dates (following Jerry's instructions) and show us the code that gets generated by the macro recorder we can amend it to repeat itself for each date it finds in column A.

 

[jbeaucaire]

.... or give you a popup where you can enter the date you want, then the autofilter will run/copy on its own for that date...

 

[phillypdh]

here is the code that was returned from the recorder.

Sub Macro4()
'
' Macro4 Macro
'
'
   
    Rows("2:2").Select
    Selection.AutoFilter
    ActiveWindow.LargeScroll ToRight:=-1
    ActiveSheet.Range("$A$2:$N$24459").AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "8/22/2011")
    ActiveWindow.SmallScroll Down:=-15
    Range("B3870:D3870").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
End Sub

 

[phillypdh]

thanks for the guidance guys i think i have it figured out!