Hey All,
I have a few reports that i inherited and i need to change the code a bit..
Right now it tells excel that if the date is double clicked that it will run the macros and drop the data specific to that date.
I am looking to change this to automatically look for the first empty cell is column C that has the Monday through Friday in column A and drop the data from that date in automatically without the double click.
The code I am looking to modify is pretty the same for all of the reports. Help is greatly appreciated.
My end goal is to be able to make a VBS script that that will open the book, run this macro and update the book automatically without human intervention.
I have a few reports that i inherited and i need to change the code a bit..
Right now it tells excel that if the date is double clicked that it will run the macros and drop the data specific to that date.
I am looking to change this to automatically look for the first empty cell is column C that has the Monday through Friday in column A and drop the data from that date in automatically without the double click.
The code I am looking to modify is pretty the same for all of the reports. Help is greatly appreciated.
My end goal is to be able to make a VBS script that that will open the book, run this macro and update the book automatically without human intervention.
Code:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Application.Calculation = xlCalculationManual
If (ActiveCell.Column = 2) And (ActiveCell.Value < Date) Then
Do Until (ActiveCell.Value = Date) Or (ActiveCell.Row > 435)
RC = ActiveCell.Row
d = ActiveSheet.Cells(ActiveCell.Row, 1).Text
If ActiveSheet.Cells(ActiveCell.Row, 1).Text = "Monday" Then GoTo PopRow
If ActiveSheet.Cells(ActiveCell.Row, 1).Text = "Tuesday" Then GoTo PopRow
If ActiveSheet.Cells(ActiveCell.Row, 1).Text = "Wednesday" Then GoTo PopRow
If ActiveSheet.Cells(ActiveCell.Row, 1).Text = "Thursday" Then GoTo PopRow
If ActiveSheet.Cells(ActiveCell.Row, 1).Text = "Friday" Then GoTo PopRow
If ActiveSheet.Cells(ActiveCell.Row, 1).Text = "Saturday" Then GoTo PopRow
If ActiveSheet.Cells(ActiveCell.Row, 1).Text = "Sunday" Then GoTo PopRow
GoTo NoPopRow
PopRow:
ENT_SPLITS
CVG_SPLITS
NoPopRow:
ActiveSheet.Cells(ActiveCell.Row + 1, 2).Select
Loop
End If
ActiveSheet.Cells(1, 1).Select
Application.Calculation = xlCalculationAutomatic
End Sub