When I run the VBA code below, it provides me with the results I require. However after…
- 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
- 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 = TrueEnd Sub
Make sure to replace "CTasks" with the correct worksheet name if needed.
- User
thanks, your correction worked
- 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.