G
Guest
Hi,
I am tryihg to compare the worksheet names between two worksheets to a list
on the main page. If it does not exist on the main page list the sheet name
is placed on the maintenance page. I get a method range of object worksheet
failed message. I've been trying to find an explanation with not luck. What
does this error mean and how do I fix it?
Also, I've sometimes seen the Set cleared with Set Whatever = Nothing, but I
usually don't see it. What is the best practice and what are the
ramifications if it's done vs not done?
Thanks for your help.
Dim ws As Worksheet
Dim FirstIndex As Long
Dim LastIndex As Long
Dim Nuserow As Long
Dim Lusedrow As Long
Dim CellRange As Range
'get the last cell on Main Page for the sheet names in BD
Lusedrow = MainPagepg.Cells(Rows.Count, "BD").End(xlUp).Row
Set CellRange = MainPagepg.Range("BD13:BD" & Lusedrow)
FirstIndex = Firstpg.Index
LastIndex = Lastpg.Index
ShNotLogged = False
For Each ws In ThisWorkbook.Worksheets
If ws.Index > FirstIndex Then
If ws.Index < LastIndex Then
If (IsError(Application.WorksheetFunction.Match(ws.Name,
CellRange, 0))) Then
With Maintenancepg
'find the next empty cell on maintenance page
Nuserow = Maintenancepg.Cells(Rows.Count,
"B").End(xlUp).Row + 1
'save the missing sheet name
Maintenancepg.Cells(Nuserow, 2).Value = ws.Name
ShNotLogged = True
End With
End If
End If
End If
Next ws
I am tryihg to compare the worksheet names between two worksheets to a list
on the main page. If it does not exist on the main page list the sheet name
is placed on the maintenance page. I get a method range of object worksheet
failed message. I've been trying to find an explanation with not luck. What
does this error mean and how do I fix it?
Also, I've sometimes seen the Set cleared with Set Whatever = Nothing, but I
usually don't see it. What is the best practice and what are the
ramifications if it's done vs not done?
Thanks for your help.
Dim ws As Worksheet
Dim FirstIndex As Long
Dim LastIndex As Long
Dim Nuserow As Long
Dim Lusedrow As Long
Dim CellRange As Range
'get the last cell on Main Page for the sheet names in BD
Lusedrow = MainPagepg.Cells(Rows.Count, "BD").End(xlUp).Row
Set CellRange = MainPagepg.Range("BD13:BD" & Lusedrow)
FirstIndex = Firstpg.Index
LastIndex = Lastpg.Index
ShNotLogged = False
For Each ws In ThisWorkbook.Worksheets
If ws.Index > FirstIndex Then
If ws.Index < LastIndex Then
If (IsError(Application.WorksheetFunction.Match(ws.Name,
CellRange, 0))) Then
With Maintenancepg
'find the next empty cell on maintenance page
Nuserow = Maintenancepg.Cells(Rows.Count,
"B").End(xlUp).Row + 1
'save the missing sheet name
Maintenancepg.Cells(Nuserow, 2).Value = ws.Name
ShNotLogged = True
End With
End If
End If
End If
Next ws