Macro for copying the value of one Cell to another cell(s) in specific time interval

Hi, I am new to this Forum and also in writing Macros.

So, I have managed to write this Macro to copy the value of J2 to K2 and subsequently to L2..M2..N2... where the updated value of J2 is being recorded in subsequent cells at 1 minute's interval.

Sub MntCalculate()
lc = Cells(2, Columns.Count).End(xlToLeft).Column + 1
If lc < 11 Then lc = 11
For i = 0 To 0
Cells(i + 2, lc) = Range("J2")
Next i
Application.OnTime Now + TimeValue("00:01:00"), "Sheet1.MntCalculate"
End Sub

My question is, how do I now modify this Macro to also copy the values of J3 to K3..L3..M3..N3.. and J4 to K4..L4..M4..N4... upto J218 to K218..L218..M218..N218... ?

Thanks.

Maybe:

Sub MntCalculate() 'Swarnendu
LC = Application.MAX(11, Cells(2, Columns.Count).End(xlToLeft).column + 1)
For i = 2 To 218
Cells(i, LC) = Range("J" & i)
Next i
Application.OnTime Now + TimeValue("00:01:00"), "Sheet1.MntCalculate"
End Sub

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Thanks a ton!! it works perfectly!!

Now, how can I program this Macro to start a 9.00 AM and stop at 3.30 PM everyday from Monday to Friday?

Thanks once again..

You're welcome and thanks for the rep!

I'll have to research your question as I don't know offhand

Hi again..

I think I have managed to write the code for starting the macro at 9AM

I have written this on Thisworkbook:

Private Sub Workbook_Open()
Application.OnTime TimeValue("09:00:00"), "Sheet1.MntCalculate"
End Sub

And, this works!!

Now, how to stop the Macro at 3.30PM?
If I replace the last line of my "MntCalculate" Macro with

If Time >= #3:30:00 PM# Then End Sub

will it work?

And, how to NOT run the Macro on Saturdays & Sundays??
And, How to automatically open this workbook at 9AM???

Thanks,

Swarnendu

I'm Guessing:

Private Sub Workbook_Open()
If Day(Date) > 1 And Day(Date) < 7 Then
Application.OnTime TimeValue("09:00:00"), _
"Sheet1.MntCalculate", TimeValue("15:30:00")
Else
Application.OnTime TimeValue("09:00:00"), _
"Sheet1.MntCalculate", TimeValue("15:30:00"),,False
End If
End Sub

You're welcome again Hope it works.

Private Sub Workbook_Open()
If Day(Date) > 1 And Day(Date) < 7 Then
Application.OnTime TimeValue("09:00:00"), _
"Sheet1.MntCalculate", TimeValue("15:30:00")
Else
Application.OnTime TimeValue("09:00:00"), _
"Sheet1.MntCalculate", TimeValue("15:30:00"),False
End If
End Sub

* There may have been an extra comma in the code

From what I've read, we need to find a way to stop it.

My bad - it should be weekday.

Maybe:

Private Sub Workbook_Open()
If Weekday(Date) > 1 And Weekday(Date) < 7 Then
Application.OnTime TimeValue("09:00:00"), _
"Sheet1.MntCalculate", TimeValue("15:30:00")
Else
On Error Resume Next
Application.OnTime TimeValue("09:00:00"), _
"Sheet1.MntCalculate", TimeValue("15:30:00"),False
End If
End Sub

Well, these are my latest Macros, and everything seems to be working fine:

Workbook:

Private Sub Workbook_Open() 
    If Weekday(Date) <> 1 And Weekday(Date) <> 7 Then 
        Application.OnTime Now, "Sheet7.MntCalculate" 
    End If 
End Sub

Sheet7:

Sub MntCalculate() 
    If TimeValue(Now) > TimeValue("08:55:00") And TimeValue(Now) < TimeValue("09:15:00") Then 
        Range("L2:NW218").ClearContents 
        Application.OnTime Now + TimeValue("00:00:01"), "Sheet7.MntCalculate" 
    Else 
        If TimeValue(Now) > TimeValue("09:14:59") And TimeValue(Now) < TimeValue("15:30:01") Then 
            Application.Calculation = xlCalculationAutomatic 
            ActiveWorkbook.RefreshAll 
            LC = Application.Max(11, Cells(2, Columns.Count).End(xlToLeft).Column + 1) 
            For i = 2 To 218 
                Cells(i, LC) = Range("J" & i) 
            Next i 
            Application.OnTime Now + TimeValue("00:01:00"), "Sheet7.MntCalculate" 
        Else 
            Application.OnTime Now + TimeValue("00:00:01"), "Sheet7.MntCalculate" 
        End If 
    End If 
End Sub

Thank you xladept for all the help you provided.

I'll come back to start another thread if I still face any problems with the codes.

Swarnendu

Great! Glad you got it working! You're welcome!