Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

When I run the VBA code below, it provides me with the results I require. However after…

  1. User

    When I run the VBA code below, it provides me with the results I require. However after retrieving the results, I get an 'Object Required' error and the "Service Providers History" workbook is not closed

    Sub FindAndCopyHRows()

    ' Unlock Sheet ActiveSheet.Unprotect Password:="edit" Application.EnableEvents = False Dim selectedValue As String Dim wb As Workbook Dim ws As Worksheet Dim hTasksWs As Worksheet Dim lastRow As Long, pasteRow As Long Dim sortRange As Range Set wb = ThisWorkbook ' Get the selected value from cell A1 selectedValue = wb.Worksheets("CTasks").Range("A1").Value ' Set the "HTasks" worksheet as the destination for copied rows Set hTasksWs = wb.Worksheets("CTasks") ' Clear the previous data in HTasks worksheet hTasksWs.Range("B2:J" & hTasksWs.Cells(Rows.count, "B").End(xlUp).Row).Clear hTasksWs.Range("K2:K" & hTasksWs.Cells(Rows.count, "K").End(xlUp).Row).Clear ' Open the "Service Providers History" workbook in the background Application.ScreenUpdating = False Application.DisplayAlerts = False Dim spHistoryWorkbook As Workbook Set spHistoryWorkbook = Workbooks.Open(fileName:="F:\FACMNG\SERVICE PROVIDERS\Service Providers History.xlsx", UpdateLinks:=False) ' Loop through all sheets in the "Service Providers History" workbook For Each ws In spHistoryWorkbook.Worksheets ' Check if the sheet name has only three letters If Len(ws.Name) = 3 Then ' Find the matching value in column B lastRow = ws.Cells(ws.Rows.count, "B").End(xlUp).Row For i = 2 To lastRow If ws.Cells(i, "B").Value = selectedValue Then ' Copy the row B:J to HTasks sheet pasteRow = hTasksWs.Cells(hTasksWs.Rows.count, "B").End(xlUp).Row + 1 ws.Range("B" & i & ":J" & i).Copy Destination:=hTasksWs.Range("B" & pasteRow & ":J" & pasteRow) ' Write the sheet name to column K in CTasks sheet hTasksWs.Cells(pasteRow, "K").Value = ws.Name End If Next i End If Next ws ' Calculate column L in HTasks sheet hTasksWs.Range("N1:X1").Copy Destination:=hTasksWs.Range("B1:L1") hTasksWs.Range("A21").Value = "" hTasksWs.Range("A24").Value = "HISTORY TASKS"

    ' Sort the data in CTasks worksheet cTasksWs.Sort.SortFields.Clear If selectedValue = "Service" Then cTasksWs.Sort.SortFields.Add Key:=cTasksWs.Range("H2:H" & cTasksWs.Cells(Rows.count, "H").End(xlUp).Row), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal Else cTasksWs.Sort.SortFields.Add Key:=cTasksWs.Range("I2:I" & cTasksWs.Cells(Rows.count, "I").End(xlUp).Row), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal End If With cTasksWs.Sort .SetRange cTasksWs.Range("B2:K" & cTasksWs.Cells(Rows.count, "B").End(xlUp).Row) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With hTasksWs.Range("B1:L1").Interior.Color = RGB(255, 0, 0) ' Red hTasksWs.Range("L2:L202").Calculate cTasksWs.Range("Y2:Z202").Calculate spHistoryWorkbook.Close SaveChanges:=False Application.ScreenUpdating = True Application.DisplayAlerts = True hTasksWs.Range("A24").Select ' Lock Sheet ActiveSheet.Protect Password:="edit" Application.EnableEvents = True End Sub

  2. Assistant

    The issue with the code is that it is referencing a worksheet variable called "cTasksWs" which is not defined. This causes an "Object Required" error later in the code when trying to sort the data in the CTasks worksheet.

    To fix this issue, you can define the "cTasksWs" worksheet variable and set it to the correct worksheet before sorting the data. Here's an updated version of the code:

    Sub FindAndCopyHRows()

    ' Unlock Sheet
    ActiveSheet.Unprotect Password:="edit"
    Application.EnableEvents = False
    Dim selectedValue As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim hTasksWs As Worksheet
    Dim cTasksWs As Worksheet
    Dim lastRow As Long, pasteRow As Long
    Dim sortRange As Range
    Set wb = ThisWorkbook
    ' Get the selected value from cell A1
    selectedValue = wb.Worksheets("CTasks").Range("A1").Value
    ' Set the "HTasks" worksheet as the destination for copied rows
    Set hTasksWs = wb.Worksheets("CTasks")
    Set cTasksWs = wb.Worksheets("CTasks") ' Change this to the correct worksheet name if needed
    ' Clear the previous data in HTasks worksheet
    hTasksWs.Range("B2:J" & hTasksWs.Cells(Rows.Count, "B").End(xlUp).Row).Clear
    hTasksWs.Range("K2:K" & hTasksWs.Cells(Rows.Count, "K").End(xlUp).Row).Clear
    ' Open the "Service Providers History" workbook in the background
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim spHistoryWorkbook As Workbook
    Set spHistoryWorkbook = Workbooks.Open(fileName:="F:\FACMNG\SERVICE PROVIDERS\Service Providers History.xlsx", UpdateLinks:=False)
    ' Loop through all sheets in the "Service Providers History" workbook
    For Each ws In spHistoryWorkbook.Worksheets
        ' Check if the sheet name has only three letters
        If Len(ws.Name) = 3 Then
            ' Find the matching value in column B
            lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
            For i = 2 To lastRow
                If ws.Cells(i, "B").Value = selectedValue Then
                    ' Copy the row B:J to HTasks sheet
                    pasteRow = hTasksWs.Cells(hTasksWs.Rows.Count, "B").End(xlUp).Row + 1
                    ws.Range("B" & i & ":J" & i).Copy Destination:=hTasksWs.Range("B" & pasteRow & ":J" & pasteRow)
                    ' Write the sheet name to column K in CTasks sheet
                    hTasksWs.Cells(pasteRow, "K").Value = ws.Name
                End If
            Next i
        End If
    Next ws
    ' Calculate column L in HTasks sheet
    hTasksWs.Range("N1:X1").Copy Destination:=hTasksWs.Range("B1:L1")
    hTasksWs.Range("A21").Value = ""
    hTasksWs.Range("A24").Value = "HISTORY TASKS"
    
    ' Sort the data in CTasks worksheet
    cTasksWs.Sort.SortFields.Clear
    If selectedValue = "Service" Then
        cTasksWs.Sort.SortFields.Add Key:=cTasksWs.Range("H2:H" & cTasksWs.Cells(Rows.Count, "H").End(xlUp).Row), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    Else
        cTasksWs.Sort.SortFields.Add Key:=cTasksWs.Range("I2:I" & cTasksWs.Cells(Rows.Count, "I").End(xlUp).Row), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End If
    With cTasksWs.Sort
        .SetRange cTasksWs.Range("B2:K" & cTasksWs.Cells(Rows.Count, "B").End(xlUp).Row)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    hTasksWs.Range("B1:L1").Interior.Color = RGB(255, 0, 0) ' Red
    hTasksWs.Range("L2:L202").Calculate
    cTasksWs.Range("Y2:Z202").Calculate
    spHistoryWorkbook.Close SaveChanges:=False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    hTasksWs.Range("A24").Select
    ' Lock Sheet
    ActiveSheet.Protect Password:="edit"
    Application.EnableEvents = True
    

    End Sub

    Make sure to replace "CTasks" with the correct worksheet name if needed.

  3. User

    thanks, your correction worked

  4. Assistant

    You're welcome! I'm glad to hear that the correction worked for you. If you have any more questions or need further assistance, feel free to ask.