ERROR OBJECT REQUIRED

[srikanth sare]

Private Sub St()
On Error GoTo EH
Application.Run "TurnOff"

Sheet8.Range("A:CR").EntireColumn.Hidden = False
Dim K As Long
For K = 0 To 15
Sheet8.Range("AE:AE,BG:BG").Offset(, K).EntireColumn.Hidden = Sheet2.Range("AF38").Offset(K).Value = "NO"
Sheet8.Range("S:S").EntireColumn.Hidden = Sheet2.Range("AC52").Value = "NO"
Next
Sheet8.Range("CT:EB").EntireColumn.Hidden = True

Sheet5.Unprotect "1818"
Sheet5.Cells.Copy
Sheet6.Range("A1").PasteSpecial xlPasteValues
Sheet6.Range("A1").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
Sheet8.Range("A6:CR9999").Clear

Dim xRg, raSource As Range
Dim i, N As Long
Dim MyValue As Variant

i = Sheet6.UsedRange.Rows.Count
Set xRg = Sheet6.Range("B6:B" & i)

MyValue = Sheet5.Range("L1").Value
For N = 1 To xRg.Rows.Count
For Each KCELL In Intersect(xRg, xRg.Rows(N).EntireRow)
If KCELL.Value = MyValue Then
If raSource Is Nothing Then
Set raSource = Range(Cells(KCELL.Row, 1), Cells(KELL.Row, 96)) ' ERROR AT THIS LINE OBJECT REQUIRED
Else
Set raSource = Union(raSource, Range(Cells(KCELL.Row, 1), Cells(KELL.Row, 96)))
End If
Exit For
End If
Next
Next N

raSource.Copy
Sheet8.Range("A6").PasteSpecial xlPasteAllUsingSourceTheme
Application.CutCopyMode = False
Sheet8.Activate

CleanUp: On Error Resume Next
Application.Run "TurnOn"
Sheet6.Cells.Clear
Sheet5.Protect "1818", DrawingObjects:=False, Contents:=True, Scenarios:=False, UserInterFaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True
Exit Sub
EH: Debug.Print Err.Description ' Do error handling
MsgBox "Sorry, an error occured." & vbCrLf & Err.Description, vbCritical, "Error!"
Resume CleanUp
End Sub

 

[StephenCrump]

You have a typo:

Set raSource = Range(Cells(KCELL.Row, 1), Cells(KCELL.Row, 96)) ' ERROR AT THIS LINE OBJECT REQUIRED

Luckily for you, you got an error this time. But often, typos will make your code behave in unexpected ways and give incorrect results.

I recommend you always use Option Explicit!

By the way, your unqualified references to Cells() will refer to the ActiveSheet. That may not be what you want?

 

[srikanth sare]

You have a typo:

Set raSource = Range(Cells(KCELL.Row, 1), Cells(KCELL.Row, 96)) ' ERROR AT THIS LINE OBJECT REQUIRED

Luckily for you, you got an error this time. But often, typos will make your code behave in unexpected ways and give incorrect results.

I recommend you always use Option Explicit!

By the way, your unqualified references to Cells() will refer to the ActiveSheet. That may not be what you want?

that solved the problem
thank you so much