All of a sudden, this line of code is now giving me an error ' If Evaluate("ISREF('" &…
- User
All of a sudden, this line of code is now giving me an error ' If Evaluate("ISREF('" & observeSheet.Range("B" & i).Value & "'!A1)") Then '
in the following code: Sub CopyAndSortInfo() ' Define Variables Dim observeSheet As Worksheet Dim wb As Workbook Dim LastRow As Long Dim i As Long
' Set Workbook Object Set wb = ThisWorkbook
' Set Observe Sheet Object Set observeSheet = wb.Sheets("Observe")
' Get the Last Row Number with Value in Column B LastRow = observeSheet.Cells(Rows.Count, "B").End(xlUp).Row
' Loop Through the Rows For i = 2 To LastRow ' Check If Sheet Name Exists If Evaluate("ISREF('" & observeSheet.Range("B" & i).Value & "'!A1)") Then ' Get the Destination Sheet Object Dim destSheet As Worksheet Set destSheet = wb.Sheets(observeSheet.Range("B" & i).Value) ' Get the Last Row Number with Value in Column A Dim destLastRow As Long destLastRow = destSheet.Cells(Rows.Count, "A").End(xlUp).Row ' Copy The Observation to the Destination Sheet destSheet.Range("A" & destLastRow + 1).Value = observeSheet.Range("A" & i).Value ' Sort The Destination Sheet by Priority Number in Column C destSheet.Sort.SortFields.Clear destSheet.Sort.SortFields.Add2 Key:=Range("C2:C30"), SortOn:=xlSortOnValues destSheet.Sort.SortFields(1).Order = xlAscending destSheet.Sort.Apply End If Next i
Application.Wait (Now + TimeValue("0:00:02"))
Call CopyPremValues
End Sub
- Assistant
The error is likely due to a change in the structure or naming of the worksheets in the workbook. The code uses the value in column B of the "Observe" sheet to reference a worksheet with that name, but if that worksheet doesn't exist or has been renamed, the code will throw an error. Double check the worksheet names and ensure they match the values in column B.
- User
sheet name has not changed
- Assistant
In that case, the error may be due to a change in the workbook structure or settings that is affecting the
Evaluatefunction. Here are a few things you can try:-
Make sure that the workbook containing the sheets you are referencing is open. The
Evaluatefunction may not work properly if the referenced workbook is not open. -
Check if any of the sheet names in the workbook contain special characters or spaces. If so, try removing them and see if the problem persists.
-
Try replacing the
Evaluatefunction with aWorksheetFunctionthat checks if a worksheet exists. Here's an example:
If Not WorksheetFunction.IsError(Application.WorksheetFunction.Match(observeSheet.Range("B" & i).Value, wb.Sheets, 0)) ThenThis code checks if the sheet name in column B exists in the workbook by using the
Matchfunction on the collection of sheet names in the workbook'sSheetsproperty.- Check if there are any missing references or corrupted libraries in the workbook. Go to the VBA Editor, click Tools -> References, and look for any libraries that are marked as "Missing". If you find any, try fixing the references or removing the library if it's not needed.
I hope one of these steps helps resolve the issue.
-