maldroid
03-27-2018, 08:04 AM
Hallo there :hi:
please, can you help me with this issue? Basically I have to remove a sheet "Clock" from the workbook with ca 60 Sheets. But only if there are two conditions fulfilled:
1) On each worksheet there has to be text "out" in the cell J2
2) The number of rows on each worksheet has to be more than 2 (in column A)
If these conditions are fulfilled, i want to delete an entire sheet "Clock" with confirmation (without property .DisplayAlerts). The sheet "Clock" is always the first, so after the removal I want to land on the second sheet...
All sheets have to have J2="out", if there is anything else or if there are less than 3 rows on the sheet, i want a message box with text "J2<>out or rowscount<3" + name of the sheet (it could happen on more than 1 sheet and I need to know on which does the error occure - it doesn't matter if it's displayed in one message box or more after each other) and just stop, so I can make correction and run the macro again.
I'm new here so I hope it makes sence. Thank you all for your help! :hi:
I've tried these and they don't work as I need to...
Code1:
Sub WorksheetLoop()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
If ws.Range("J2") = "in" Or ws.Cells(Rows.Count, 1).End(xlUp).Row < 3 Then
MsgBox "Sheet: " & ActiveSheet.Name & vbNewLine & "J2<>out or rowscount<3"
Exit For
Else
Sheets("Clock").Delete
End If
Next ws
End Sub
Code2:
Sub WorksheetLoop()
' Declare Current as a worksheet object variable.
Dim WS_Count As Byte
Dim I As Byte
Dim H As Byte
H = 0
WS_Count = ActiveWorkbook.Worksheets.Count
' Loop through all of the worksheets in the active workbook.
For I = 1 To WS_Count
If ActiveWorkbook.ActiveSheet.Range("J2") = "in" Or ActiveWorkbook.ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row < 3 Then
H = H + 1
MsgBox "J2<>out or rowscount<3" & vbNewLine & "Sheet: " & ActiveWorkbook.ActiveSheet.Name
End If
Next I
If H = 0 Then
Sheets("Clock").Delete
End If
End Sub
please, can you help me with this issue? Basically I have to remove a sheet "Clock" from the workbook with ca 60 Sheets. But only if there are two conditions fulfilled:
1) On each worksheet there has to be text "out" in the cell J2
2) The number of rows on each worksheet has to be more than 2 (in column A)
If these conditions are fulfilled, i want to delete an entire sheet "Clock" with confirmation (without property .DisplayAlerts). The sheet "Clock" is always the first, so after the removal I want to land on the second sheet...
All sheets have to have J2="out", if there is anything else or if there are less than 3 rows on the sheet, i want a message box with text "J2<>out or rowscount<3" + name of the sheet (it could happen on more than 1 sheet and I need to know on which does the error occure - it doesn't matter if it's displayed in one message box or more after each other) and just stop, so I can make correction and run the macro again.
I'm new here so I hope it makes sence. Thank you all for your help! :hi:
I've tried these and they don't work as I need to...
Code1:
Sub WorksheetLoop()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
If ws.Range("J2") = "in" Or ws.Cells(Rows.Count, 1).End(xlUp).Row < 3 Then
MsgBox "Sheet: " & ActiveSheet.Name & vbNewLine & "J2<>out or rowscount<3"
Exit For
Else
Sheets("Clock").Delete
End If
Next ws
End Sub
Code2:
Sub WorksheetLoop()
' Declare Current as a worksheet object variable.
Dim WS_Count As Byte
Dim I As Byte
Dim H As Byte
H = 0
WS_Count = ActiveWorkbook.Worksheets.Count
' Loop through all of the worksheets in the active workbook.
For I = 1 To WS_Count
If ActiveWorkbook.ActiveSheet.Range("J2") = "in" Or ActiveWorkbook.ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row < 3 Then
H = H + 1
MsgBox "J2<>out or rowscount<3" & vbNewLine & "Sheet: " & ActiveWorkbook.ActiveSheet.Name
End If
Next I
If H = 0 Then
Sheets("Clock").Delete
End If
End Sub