Southern_Gentleman
asked on
Run macro if Workbook_Open() criteria is met
I want to run my procedure of moving row data to another worksheet. I have that but I want it to run automatically when the spreadsheet opens. If Column B date is 7 days ago from today's i want it to run my macro (TransferPrints) . For example if today is 06/05/2013 and column B has a date of 05/20/2013 (Subtract only weekdays), it would run, if it is 6/01/2013 it would not run my macro for that particular row.
I tried adding the following procedure to the workbook page but I'm having all my data in Column B removed instead of filtering and selecting only days that are greater than 7 days from today
Private Sub Workbook_Open()
On Error Resume Next
If (Application.WorksheetFunc tion.Weekd ay(Format( Now(), "m/d/yyyy") > 7)) Then
If (Format(Now(), "m,d,yyyy") = Sheets("Print").Range("B:B ").Value) Then
'If (WorksheetFunction.WorkDay (Sheets("P rint").Ran ge("B:B"), -7)) Then
TransferPrints
End If
End If
End Sub
I tried adding the following procedure to the workbook page but I'm having all my data in Column B removed instead of filtering and selecting only days that are greater than 7 days from today
Private Sub Workbook_Open()
On Error Resume Next
If (Application.WorksheetFunc
If (Format(Now(), "m,d,yyyy") = Sheets("Print").Range("B:B
'If (WorksheetFunction.WorkDay
TransferPrints
End If
End If
End Sub
I'm amazed that anything works at all.
This code:
In addition, there is no reason to format the value unless you want to see it differently in the debugger.
And the "Application.WorksheetFunc tion" part is optional.
So what I think you're looking for is something closer to what robberbaron posted. Ultimately, if you want to check more than one cell, you either need to use a function that supports ranges (of which neither Format nor Now do in your second If) or you need to use some kind of loop (like robberbaron's For Each)
And as indicated, the problem may be in other parts of the code.
This code:
If (Application.WorksheetFunction.Weekday(Format(Now(), "m/d/yyyy") > 7)) Then
Should always be false since the Weekday function returns a number from 1-7, which will never be > 7.In addition, there is no reason to format the value unless you want to see it differently in the debugger.
And the "Application.WorksheetFunc
So what I think you're looking for is something closer to what robberbaron posted. Ultimately, if you want to check more than one cell, you either need to use a function that supports ranges (of which neither Format nor Now do in your second If) or you need to use some kind of loop (like robberbaron's For Each)
And as indicated, the problem may be in other parts of the code.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Roberbarron, good stuff.
something like this may work....
Open in new window
can you post parts of a sample workbook for us to test against ?