Sub CM1_Stoppage(DataSheet As String, LogSheet As String)
'Cleaning the previous stoppage entries
Sheets(LogSheet).Range("P62:P79").Value = ""
Sheets(LogSheet).Range("Q62:Q79").Value = ""
Sheets(LogSheet).Range("R62:R79").Value = ""
'Cleaning the previous CCRO and Shift CO entries
Sheets(LogSheet).Range("E60:M60").Value = ""
Sheets(LogSheet).Range("E61:M61").Value = ""
'Decalaration of variables
Dim StoppageRow, Timestamp, StopStart
'Initialization of variables
StartTimeRow = 62 'Row of start of pasting (first row) on the Log sheet
StoppageRow = 12 ' Row of start of stoppage data on the data sheet
'Make Stoppage data sheet the active sheet, select Starting Stoppage Cell
Sheets(DataSheet).Activate
Sheets(DataSheet).Range("AN12").Select
Selection.Copy
Timestamp = Sheets(DataSheet).Range("AN12").Value
StopStart = Sheets(DataSheet).Range("AO12").Value
'Deciding on which row to pass first Timestamp depending on whether its a Stop Time or a Start Time
If StopStart = 0 Then
StopTime = 60 ' Row on the Logsheet to paste the first stoppage timestamp, it is firts if it is a stop
Else
Sheets(LogSheet).Cells(60, 16).Value = ""
StopTimeRow = 60 ' Row on the Logsheet to paste the first stoppage timestamp, it is second if it is a start
End If
'Copy the rest of the succeding Timestamps
While Timestamp <> ""
Timestamp = Sheets(DataSheet).Cells(StoppageRow, 40).Value
StopStart = Sheets(DataSheet).Cells(StoppageRow, 41).Value
'If its Stop Time, past in StopTime Coloumn of the LogSheet
If StopStart = 0 Then
Sheets(LogSheet).Cells().Item(StopTimeRow, 16).PasteSpecial Paste:=xlValues
StopTimeRow = StopTimeRow + 1
'If its Start Time, past in StartTime Coloumn of the LogSheet
Else
Sheets(LogSheet).Cells().Item(StartTimeRow, 17).PasteSpecial Paste:=xlValues
StartTimeRow = StartTimeRow + 1
End If
StoppageRow = StoppageRow + 1
Sheets(DataSheet).Cells().Item(StoppageRow, 40).Select
Selection.Copy
Wend
'MsgBox ("Please check that the data in the Cement Mill 3 Log sheet matches those in IP21 process explorer. In the event that it does not, inform Systems Engineer")
'Make LogSheet the active sheet
Sheets(LogSheet).Activate
End Sub
Moderator's Edit: Use code tags whenever you use code in your post. To do so, either select your code and click on the # button above or type [code] before your code and [/code] after it.
Bookmarks