Scheduling Data Collection

[cram8603]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I am using a macro to collect data on manufacturing processes every 5 minutes, Our Company has 3 shifts. On week days I can start the macro when I leave to collect data through 2<SUP>nd</SUP> and 3<SUP>rd</SUP> shift. I need data from certain shifts on the weekend, If I leave the macro running, starting Friday, I run out of columns. Is there any way to specify a start date and time for my macro, or maybe move to new sheets once the current one is filled?

 

[ravishankar]

Hi
It is possible to set a time frame or specify the location to collect data. Can you share your current codes so that it can be modified suitably?
Ravi

 

[cram8603]

Sub Data14()
Dim TargetSht As Worksheet, SourceSht As Worksheet, SourceCol As Integer, SourceCells As Range
Set SourceSht = ThisWorkbook.Sheets("TP14")
Set TargetSht = ThisWorkbook.Sheets("Data")
Set SourceCells = SourceSht.Range("N2:N19" & SourceSht.Range("B65536").End(xlUp).Row)
If TargetSht.Range("A1").Value = "" Then
SourceCol = 1
ElseIf TargetSht.Range("IV1").Value <> "" Then
MsgBox "There are no more columns available in the sheet " & TargetSht.Name, vbCritical, "No More Data Can Be Copied"
Exit Sub
Else
SourceCol = TargetSht.Range("IV1").End(xlToLeft).Column + 1
End If
TargetSht.Cells(1, SourceCol).Value = Format(Time)
SourceCells.Copy
TargetSht.Cells(2, SourceCol).PasteSpecial xlPasteValues
Application.OnTime Now + TimeValue("00:04:00"), "Data14"
Exit Sub
End Sub