Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Macros (2000)

    I have recorded the following macro. But when it gets to the point of copying the value from a cell with the folloiwng formula '=NOW() +21-WEEKDAY(NOW(),2)'. It just past the last date. The date changes each week. The date gets added to the filter located at Selection.AutoFilter Field:=24.

    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 1/9/2008 by Joe Marchini
    '

    '
    Sheets("Sheet1").Select
    Range("D7").Select <- cell that contains the above formula
    Selection.Copy
    Sheets("Sheet1").Select
    Range("B33").Select
    Sheets("Apparatus Group").Select
    Selection.AutoFilter Field:=24, Criteria1:="<2/10/2008", Operator:=xlAnd <---- This date never changes.
    End Sub

    Can I add the formula directly in the macro routine? I have tried and received errors.
    Or do I try do use command buttons which I don't believe I'm ready for.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Macros (2000)

    Welcome to Woody's Lounge!

    Try this:
    <code>
    Sub Macro1()
    Sheets("Apparatus Group").Range("A1").AutoFilter Field:=24, _
    Criteria1:="<" & Sheets("Sheet1").Range("D7"), Operator:=xlAnd
    End Sub
    </code>
    Note that the macro doesn't select any cells.

  3. #3
    New Lounger
    Join Date
    Jan 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macros (2000)

    Hans,
    Thanks for the quick reply. I received a compling error when I modified my macro.
    Attached is the excel document. Basicly it's a tool to resource work per day during a certaing week. For example T-8 would be the work schedueld eight weeks from now. Like I stated earlier, the date thats entered into 'Apparatus Group' X16 doesn't change. Each week the date changes and the plan is that it gets populated in the autofilter for that column.
    It would be a time saver if I can get this fixed. Right now I need to manually correct the dates in the macros so it works properly.
    Thanks again.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Macros (2000)

    The code I posted works correctly - I have inserted it into the attached version and tested it - but I don't understand what you're trying to accomplish.
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Jan 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macros (2000)

    Hans,
    Your correct, it works perfectly. I'm a novice so I didn't change some of the characters to work with the appropriate cell or sheet etc...
    So I copied how you had it in the excel document, modified and it works perfectly.
    Thank you very much, you’re a savior.
    Joe

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •