I'm trying to create a macro that will select a range of rows fitting
a certain criterion, then insert today's date into an empty cell in
each row that matches the criterion. I set up the macro to insert the
formula into the top cell of the range and then copy the formula down
the range. My problem is that I have to run this macro daily, and the
range of rows changes every day. I set up the macro using today's
report, so it contains today's ranges, but I need to alter the macro
so that it selects the active range for each day. Here is the
relevant
portion of the macro:


Selection.AutoFilter Field:=8, Criteria1:="Ad"
Range("L87").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.Copy
Range("L307:L5100").Select
ActiveSheet.Paste


I'm a complete novice at macros, so if anyone has any ideas for a
code
that would work, please help!


Thanks,
Jill

From the code, it looks like you're showing the Ad codes in column H and putting
today's date in column L for those rows.

If that's true, ...

Option Explicit
Sub testme()

Dim wks As Worksheet

Set wks = ActiveSheet

With wks
.AutoFilterMode = False
.Range("a1").CurrentRegion.AutoFilter field:=8, Criteria1:="Ad"

'just start with the first column (A for my data)
With .AutoFilter.Range.Columns(1)
If .Cells.Count = 1 Then
'only headers, do nothing
Else
'avoid the header row, and come over 11 columns (A -> L)
With .Resize(.Rows.Count - 1).Offset(1, 11) _
.Cells.SpecialCells(xlCellTypeVisible)
.Formula = "=today()"
End With
End If
End With
End With

End Sub


(E-Mail Removed) wrote:
>
> I'm trying to create a macro that will select a range of rows fitting
> a certain criterion, then insert today's date into an empty cell in
> each row that matches the criterion. I set up the macro to insert the
> formula into the top cell of the range and then copy the formula down
> the range. My problem is that I have to run this macro daily, and the
> range of rows changes every day. I set up the macro using today's
> report, so it contains today's ranges, but I need to alter the macro
> so that it selects the active range for each day. Here is the
> relevant
> portion of the macro:
>
> Selection.AutoFilter Field:=8, Criteria1:="Ad"
> Range("L87").Select
> ActiveCell.FormulaR1C1 = "=TODAY()"
> Selection.Copy
> Range("L307:L5100").Select
> ActiveSheet.Paste
>
> I'm a complete novice at macros, so if anyone has any ideas for a
> code
> that would work, please help!
>
> Thanks,
> Jill

--

Dave Peterson