MAcro works OK in Step into debug mode but not doing job when run from command button

You are using select and active sheet and when you run the code, it picks the sheet names wherever the cursor happens to be, not necessarily the correct sheet name. You need to properly reference your sheet names and range.
It is also faster to use a filter function than a loop.

Option Explicit

Sub SDrops()
Dim ws As Worksheet
Dim I As Long
Dim Lrw As Long
Dim rs As Long
Dim empt

Sheets("SunDps").Range("A3:G" & Application.Max(3, Sheets("SunDps").Range("A" & Rows.Count).End(xlUp).Row)).ClearContents
empt = Sheets("SunDps").Cells(1, 4).Value
I = 3
For Each ws In ThisWorkbook.Worksheets
    With ws
        If .Name = "G" Or .Name = "H" Then
        Lrw = .Range("F" & .Rows.Count).End(xlUp).Row
            For rs = 2 To Lrw
                If .Cells(rs, 3).Value = empt And .Cells(rs, 7).Value <> "y" Then
                    .Cells(rs, 3).EntireRow.Copy
                    Sheets("SunDps").Range("A" & I).PasteSpecial Paste:=xlPasteValues
                    I = I + 1
                End If
            Next rs
        End If
    End With
Next ws
End Sub

Hi !

Better & faster execution just using a filter or an advanced filter …

ah, I forgot to remove "ws.Activate", in "With ws" section. It works Now. Thanks

Hi AB33

I did this;

empt = Sheets("SunDps").Cells(1, 4).Value

and also put

With ws

but it doesn't help. Even when I run macro from Visual Basic window's Run button, it works.

Same problem happening to another code. Code won't run using button in sheet. What is issue in it now?

Sub Shfts()

Dim SMin As Long, SMax As Long
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook

With wb
    For Each ws In wb.Worksheets
        With ws
            If .Name = "G" Or .Name = "H" Then
                SMin = Application.Min(.Range("B:B"))
                SMax = Application.Max(.Range("B:B"))
        
    'Filling Shift tables with all shifts and deleting extra shifts
                If SMin + SMax <> 0 Then
                    With wb.Sheets("Shifts")
                        If ws.Name = "G" Then
                            tb = "Table1"
                        ElseIf ws.Name = "H" Then
                            tb = "Table2"
                        End If
                        
                        With .ListObjects(tb)
                            If (tb = "Table1" And Application.Sum(Range("A:A")) > 0) Or _
                            (tb = "Table2" And Application.Sum(Range("F:F")) > 0) Then
                                Do While .DataBodyRange(1, 1).Value > SMin
                                    .ListRows.Add (1)
                                    .DataBodyRange(1, 1).Value = .DataBodyRange(2, 1).Value - 1
                                Loop
                            End If
                            
                            If (tb = "Table1" And Application.Sum(Range("A:A")) = 0) Or _
                            (tb = "Table2" And Application.Sum(Range("F:F")) = 0) Then
                                LstRc = 1
                                TMax = 0
                                .ListRows.Add AlwaysInsert:=True
                                .DataBodyRange(LstRc, 1).Value = SMin
                            Else
                                ' To get last record for a table we exclude 2 heading rows.
                                LstRc = .DataBodyRange(1, 1).End(xlDown).Row - 1 - 2
                                TMax = .DataBodyRange(LstRc, 1).Value
                            End If

                            If TMax < SMax Then
                                For i = TMax To SMax - 1
                                    .ListRows.Add AlwaysInsert:=True
                                    .DataBodyRange(LstRc + 1, 1).Value = .DataBodyRange(LstRc, 1).Value + 1
                                    LstRc = .DataBodyRange(1, 1).End(xlDown).Row - 1 - 2
                                Next i
                            End If
                        End With
                    End With
                End If
                    If .Name = "G" Then
                        co = 0
                    Else
                        co = 5
                    End If
                        .Range("C2:C" & Range("C2").End(xlDown).Row).FormulaR1C1 = "=IF(RC[-1]=""Open"", ""x"",VLOOKUP(RC[-1],Shifts!C[" & -2 + co & "]:C[" & -1 + co & "],2,FALSE))"
            End If
        End With
    Next ws
End With

End Sub