VBA code only working correct in debug.mode

Question

my VBA code is copy/pasting rows from several sheets in the workbook into another sheet based on a specific input criteria. It uses an InStr search to find the input criteria on sheets starting with "E" in column D between rows 17-50 - which is working good.

However, when activiting the sub through a button it only copy/pasts the first entry it finds and jumps to the next worksheet. In debug.mode it finds all entries in one worksheet, does copy/paste and only then jumps to the next worksheet.

What do I need to change?

Sub request_task_list()

Dim rPlacementCell As Range
Dim myValue As Variant
Dim i As Integer, icount As Integer

myValue = InputBox("Please enter the Name (Name or Surname) of the Person whos task you are looking for", "Input", "Hansen")
    If myValue = "" Then
        Exit Sub
    Else
        Set rPlacementCell = Worksheets("Collect_tool").Range("A3")
        For Each Worksheet In ActiveWorkbook.Worksheets

        'Only process if the sheet name starts with 'E'
        If Left(Worksheet.Name, 1) = "E" Then
            Worksheet.Select
                For i = 17 To 50

                    If InStr(1, LCase(Range("D" & i)), LCase(myValue)) <> 0 Then
                        'In string search for input value from msg. box
                        'Copy the whole row if found to placement cell
                        icount = icount + 1
                        Rows(i).EntireRow.Copy
                        rPlacementCell.PasteSpecial xlPasteValuesAndNumberFormats
                        Range("D2").Copy
                        rPlacementCell.PasteSpecial xlPasteValues
                        Set rPlacementCell = rPlacementCell.Offset(1)
                    End If
                Next i           
        End If
    Next Worksheet
Worksheets("collect_tool").Activate
Range("B3").Activate

End If

End Sub

i think you need to put your If Left(Worksheet.Name, 1) = "E" Then statement inside the for loop — mojo3340
– mojo3340, Commented Nov 22, 2016 at 11:14
Wow, that seems to work just fine. What might be the reasons it worked with my above code in debug.mode? — FlightPlanner
– FlightPlanner, Commented Nov 22, 2016 at 11:58

Darren Bartrup-Cook · Accepted Answer · 2016-11-22 11:49:40Z

This code works for me:

Sub request_task_list()

    Dim rPlacementCell As Range
    Dim myValue As Variant
    Dim i As Integer
    Dim wrkBk As Workbook
    Dim wrkSht As Worksheet

    Set wrkBk = ActiveWorkbook
    'or
    'Set wrkBk = ThisWorkbook
    'or
    'Set wrkBk = Workbooks.Open("C:/abc/def/hij.xlsx")


    myValue = InputBox("Please enter the Name (Name or Surname) of the Person whos task you are looking for", "Input", "Hansen")
    If myValue <> "" Then
        Set rPlacementCell = wrkBk.Worksheets("Collect_tool").Range("A3") 'Be specific about which workbook the sheet is in.
        For Each wrkSht In wrkBk.Worksheets
            'Only process if the sheet name starts with 'E'
            If Left(wrkSht.Name, 1) = "E" Then
                For i = 17 To 50
                    'Cells(i,4) is the same as Range("D" & i) - easier to work with numbers than letters in code.
                    If InStr(1, LCase(wrkSht.Cells(i, 4)), LCase(myValue)) > 0 Then 'Be specific about which sheet the range is on.
                        'In string search for input value from msg. box
                        'Copy the whole row if found to placement cell
                        wrkSht.Rows(i).EntireRow.Copy
                        rPlacementCell.PasteSpecial xlPasteValuesAndNumberFormats
                        rPlacementCell.Value = wrkSht.Cells(2, 4).Value
                        Set rPlacementCell = rPlacementCell.Offset(1)
                    End If
                Next i
            End If
        Next wrkSht
        Worksheets("collect_tool").Activate
        Range("B3").Activate
    End If

End Sub

I'm guessing your code failed at this point: For Each Worksheet In ActiveWorkbook.Worksheets. Worksheet is a member of the Worksheets collection and I don't think it can be used this way. Note in my code I've set wrkSht as a Worksheet object and then used wrkSht to reference the current worksheet in the loop.

Thanks! this works as well and looks a lot smoother in operating mode. And thanks for the explanation.

Collectives™ on Stack Overflow

VBA code only working correct in debug.mode

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related