Autofilter using macro

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

0
jsurkin (1)
3/5/2007 5:51:26 PM
excel 39879 articles. 2 followers. Follow

1 Replies
255 Views

Similar Articles

[PageSpeed] 37

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


jsurkin@gmail.com 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
0
petersod (12004)
3/5/2007 6:31:50 PM
Reply:

Similar Artilces:

Macro unhides personal.xls
This is my code, why does the first thing it does is unhide personal.xls. This code is in a module in personal.xls. Thanks Bill Sub CopyTest() ActiveWindow.Zoom = 75 Cells.Find(what:="Reference:").Activate ActiveCell.Offset(0, 1).Select ActiveCell.Copy MsgBox "Press the Enter key to continue" Application.OnKey "~", "'StartSub " & 0 & ",False'" Cells.Find(what:="Comments:").Activate ActiveCell.Select Dim Rng As Range On Error Resume Next Set Rng = Application.InputBox(prompt:="Choose A Range", Type:=8) Rng...

Autonew macro won't run in Word 2007
Hi Word Gurus, We are about to roll out Office 2007 across our organisation. One of our users who is testing the software is running into issues with a special template that typically runs an autonew macro when it is opened from within Word. The security in Word is set to disable macros, but when you enable the content via the option that appears, the autonew macro won't run, so the initial form doesn't appear. The template doesn't work properly within Word when we use File, Open command. However, on testing it myself I noticed it will work OK when run from W...

Record COUNT missing when using Auto-Filter??
When I use Auto-Filter on my spreadsheet, and then actually filter one of the columns, is there a way I can have Excel tell me the number of matching records? For some reason, I thought this used to show up in the status bar (lower left), but I can't get it to tell me the number of matching records anymore. HELP! Thanks a bunch! -JEN;-) Just use SUBTOTAL(3,Range) on any range that will always have data in it in your records. This simply counts the non-blank records in any range so by default will give you a count of your filtered data, -- Regards Ken....................

Custom macro and custom button
In the excel 2003, i used to have a few specific macros i created and saved in my Personal.xls file. These macros were basically setup to format a specific cell a certain way. I then had added a few "Custom" buttons for the toolbar and assigned each of these macros to a specific button. That way, i could just push a button on the toolbar, and it would run my macro and format the highlighted cells the way I wanted. Can anyone tell me how to do this in Excel 2007? thanks V If just a few you can add them to the QAT. Button>Excel Options>Customize>Choos...

Discount using two different items?
Hello, I have two different items one sellf for $11.99 and the other for $3.99 is there any way to set it up so that if a customer buys the item for $11.99 that they can get the $3.99 item for $2.99 -- Thank You,; Vince Did you try to setup Mix and Match discount? Jamil. "Vince" wrote: > Hello, I have two different items one sellf for $11.99 and the other for > $3.99 is there any way to set it up so that if a customer buys the item for > $11.99 that they can get the $3.99 item for $2.99 > -- > Thank You,; > Vince ...

Macro Not Working #2
Guys, many thanks for your help and suggestion my Macro now working. Having said that the same code works on my home PC but for some strang reason not on my Works PC - very Odd !! Thanks again Rob ; -- robertgu 44 Married two kid ----------------------------------------------------------------------- robertguy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=596 View this thread: http://www.excelforum.com/showthread.php?threadid=26381 ...

autofilter 04-19-10
in this photo we have three rows but there are blanks between them [IMG]http://i87.photobucket.com/albums/k137/kylefoley76/Picture3-13.png[/IMG] in this photo there are no blanks because i autofiltered the blanks between them. [IMG]http://i87.photobucket.com/albums/k137/kylefoley76/Picture4-8.png[/IMG] i want to autofilter the first photo and copy a b c while autofiltered but excel won't let me do that. the real spread sheet is 8000 rows long with huge gaps between the filled cells so i really can't do that unless i use the autofilter Select all your data + co...

error using leban's ReportToPDF
Hi, and sorry for my english I would like use the ReportToPDF functions in one of my projects. When I try to generate the pdf from an access report i'm getting the following error: error '2282': The format in which you are attempting to output the current object is not available It seems that the problem is in the DoCmd.OutputTo sentence when the program is going to create the previous snapshot. I'm able of export any report to snapshot, but when I try to do it with the DoCmd.OutputTo command, I get the specified error. Any help? thanks This is an Access non Western ...

Macro renaming
When creating a macro I don't always give it a descriptive name. I would like to know how to rename the macro. THanks, -- G Teachman Soil Scientist Press Alt+F8 to open the Macros dialog. Select the macro you want, and click the Edit button to open the macro editor. The first line of the macro contains its name, between the keyword "Sub" and a pair of parentheses. Change the name there, and then click the Save button. For example, to rename Macro1 as MyFirstMacro, change the line Sub Macro1() to Sub MyFirstMacro() -- Regards, Jay Freedman Micr...

Custom view & macro
Hi to all, I have a "custom view" named "main_cat" that hides some rows and colomns. how can have this done using a macro to show this view of the sheet. Did you try recording a macro when you did View|Custom View, selected your view and chose Show? (Those are the menu options in xl2003--not sure where it's hidden in xl2007.) Khalil Handal wrote: > > Hi to all, > I have a "custom view" named "main_cat" that hides some rows and colomns. > how can have this done using a macro to show this view of the sheet. -- Dave Peterson Hi, ...

Using Hyperlinks within Userforms
Hi all, I have a Userform with various command buttons in place. What I'm trying to do is allow users to be able to open various Word documents when they click on these command buttons via the use of hyperlinks embedded within the click event of each command button. So if they click on one of the command buttons it opens one Word document, and if they click another, it opens the second Word document etc. etc. It would be ideal to have the Userform to always remain displayed, so the documents open in the background, and the Userform always has the focus... then once the desired documents...

HELP with Macros
hi, I need some help with recording a macro but every time I try to recor the macro in the personal macro workbook, I get a pop stating that th personal workbook needs to be opened in the start up folder in order t record, but I don't know how to open it. I would appreciate an suggestions. Thanks, Call -- call ----------------------------------------------------------------------- calli's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3756 View this thread: http://www.excelforum.com/showthread.php?threadid=57194 save the personal.xls at C:\program file...

Can I use VBA to add cells (over blanks) then do multiplication
I have a Word table in which the last column contains numbers (3 and 4) and some bank cells and I want it add them and put the total into the second last row (7 in this case). The last row contains a multiplier (3) which when applied to the total results in 21. Below is the table. | | | 3 | | | | | | | | 4 | | | | 7 | | | 3 |21| How can I achieve this in VBA (under Word 2003 and 2007) remembering that the user can add rows to the table and the last column can contain blank cells. Thanks in advance for any assistance, Peter Evans Sub ScratchMaco(...

How you use your queue
hi, Will like to know how everyone uses the queue function. For example, we have a queue called support@company.com We will like the emails to be split equally among 5 customer service representatives. Example, if there are 500 emails entering support queue each day, each csr should be assigned 100 emails? any idea how this can be achiveed? many thanks Alan Sounds like you need to write code to do this. One approach would be to store a list of the users who these mails should go to and then also store the name of the one who got the last mail. You then write code to monitor the...

need a refresher: text boxes on charts that use relational formulas?
I could have sworn that a long time ago, I set up one or more text boxes on a graph that had formulas in them, so the text that was showing would automatically change each month. I'm now using Win2000/XL2003, and not having much luck- I want to put my legend on the bottom, then a text box under it where I will show 3 values (one for each legend item) to provide some additional information on the graph. I have too many graphs to do this and have to update it all manually. What is the best approach to use, to link the contents of my text box (or similar) to the contents of a cell that on m...

Macro for 'Paste Special'
Is there a way to set a macro up so that perform a 'paste special' into whatever cell is currently highlited? Thanks in advance. On Oct 23, 2:03=A0pm, Jeremy Leonard <Jeremy.Leon...@tecumseh.com> wrote: > Is there a way to set a macro up so that perform a 'paste special' > into whatever cell is currently highlited? > > Thanks in advance. Never mind. I figured it out. Hi, Why bother with a macro, in most cases you can put a button on the toolbar to do whatever Paste Special command you want: Value, Formulas, Links, Transpose, No Borders, or Paste Speci...

using if function..please help
i am trying to use the if function in the following manner. A B apple red apple red grapes purple grapes yellow star n/a if(B1:B5="n/a",0,countif(A1:A5, "star") I want to use this formula not only for 'star' but also for 'apple' and grapes' and many more such fruits which might have n/a later. But, everytime I use this formula it returns a 1..should it not return a 0 for when the value in B is n/a? Please help.. Thank you Are you trying to say that if any c...

outlook 2003, using windows xp professional and a small business e
hello, we are using this version as a contact managing data base and are looking to add on a pda. which pda's do you think will work best? which data will actually get sent to the pda, ie: addresses, calenders, to do's??? thank you, henry Depends on the sync software used with your PDA. "henry" <henry@discussions.microsoft.com> wrote in message news:8F126E82-FD11-4D2A-AFD9-5991034214C2@microsoft.com... > hello, > we are using this version as a contact managing data base and are looking to > add on a pda. which pda's do you think will work best? >...

Doing a VLOOKUP (probably using the INDEX and MATCH function), with both vertical and horizontal values in play.
I am trying to create a function that will pull in data from a 2nd spreadsheet. Typically, I use the index and match function to do so. However, in this case, I am trying to do a lookup based on a value above (i.e., horizontal) and a value to the right (i.e., vertical) of the cell in which the formula will be placed. Additionally, the sheet from which I am pulling is similarly laid out. To Provide an example. Lookup Table Months (horizontal) Jan Feb Mar Apr Etc Names(vertical) Jeff Eric 5 Steve ...

VBA Code for displaying time taken by a macro
hi, can you pls give me the VBA Code for diplaying the time taken by a macro to give the output. Regards, Karthik One way: Dim time1 As Double, time2 As Double time1 = Timer ' <your macro here> time2 = Timer MsgBox Format(time2 - time1, "0.00 \s\ec") In article <1123328550.673153.200890@g49g2000cwa.googlegroups.com>, "Macro man" <karthikr22@gmail.com> wrote: > hi, > > can you pls give me the VBA Code for diplaying the time taken by a > macro to give the output. > > Regards, > > Karthik Depends up...

Where to use static constructor and static class
dear friends Can you tell me in which scenario we have to use static class and static constructor? Thanks in advance Ashok kumar wrote: > dear friends > Can you tell me in which scenario we have to use static class and > static constructor? Use a static class when you want to implement functionality that does not require per-instance state (i.e. all consumers of the class can share the state). Use a static constructor when you need to initialize state in a class that is shared by all consumers of the class. Pete ...

Attempting to call a sub from another wb using a Userform option b
Please assist. I have an userform that has several options button each calls a sub proc from another workbook I am getting run-time error 1004 Method Run of object_Application failed when I click on the button when the other workbook close . When the workbook is open I am getting run-time error 1004 Cannot run the macro wbMain.xlsm!cmdOpenfile. The macro may not be available in this workbook or all macros may be disabled.but with a different reason. My macros options are enabled and has trusted access. I am baffled because the code is very simple: Application.run "...

subform used for changing mainform record
Hello. I have a main form that has a tabcontrol on it. One of the tabs has a subform on it that will list the many of the mainform's one. This is a parent/child hierarchy set up. So the parents and children are all in one table. A parent can also be a child and a child can also be a parent. Anyway, below is the code that I originally used for making the change. Now it does not and I do not see why. The button that I am using is inside the subform that when used takes and changes the mainform to the selected record and the subform as well. The txtChangeRequestID is the...

Could You Use $10,000 in the Next 30 Days.....Risk Free?
Could you use $10,000 in 30 days Risk-Free? Introducing a revolutionary new downline building system!! Free to join! Build your downline and be in MASSIVE PROFIT BEFORE you spend one single penny! You could be making over $10,000 just 30 days from now! And it's totally Risk-Free!! If you're tired of wasting money trying to make money then you will LOVE this system!! It eliminates every problem in building a downline as it's totally automated and duplicatable by everyone! This is as easy as breathing! So don't waste a second!... You could be just 30 days from Financial Freed...

triggering a macro
I need to trigger a macro when there is a change in a cell.. Hi use the worksheet_change event. See: http://www.cpearson.com/excel/events.htm -- Regards Frank Kabel Frankfurt, Germany "Sam" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:F26F8DDC-59EC-47DB-BBD0-0C079298691E@microsoft.com... > I need to trigger a macro when there is a change in a cell.. ...