Error 91

[mrinal saha]

I have this code where in i am getting error 91:

'*************************************************************************************
'****pasting the Book Date, TT and the corresponding deal name as the Heading "*****
'*************************************************************************************
Sub test()
Application.EnableCancelKey = xlDisabled
Dim lr, lastr, lastr1, i As Long
Dim ws As Worksheet
lr = Sheets("Rough").Cells(Rows.Count, 1).End(xlUp).Row

For Each ws In ThisWorkbook.Worksheets
For a = 4 To lr

If Sheets("Rough").Cells(a, 1).Value = "" Then
Exit For
ElseIf Sheets("Rough").Cells(a, 1).Value = ws.Name Then
rownum = Cells(a, 1).Row
GoTo x:
End If
Next a
y: Next ws

x:
lastr = ws.Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Rough").Activate
For i = rownum To lr
If Sheets("Rough").Cells(i, 1).Value <> Sheets("Rough").Cells(i + 1, 1).Value Then
GoTo y:
End If
If Sheets("Rough").Cells(i, 3).Value = Sheets("Rough").Cells(i - 1, 3).Value And _
Sheets("Rough").Cells(i, 4).Value <> Sheets("Rough").Cells(i - 1, 4).Value Then
Sheets("Rough").Activate
Sheets("Rough").Cells(i, 2).Resize(1, 3).Select
Selection.Interior.Color = vbRed
Selection.Copy Destination:=ws.Cells(lastr, 1)

Sheets("User provided Input").Activate
lastr1 = Sheets("User provided Input").UsedRange.Rows.Count
Range("a1:a" & lastr1).CurrentRegion.Select
Selection.Copy Destination:=ws.Cells(lastr + 1, 1)
Sheets("Rough").Activate
End If

If Sheets("Rough").Cells(i, 4).Value <> Sheets("Rough").Cells(i - 1, 4).Value Then
Sheets("Rough").Activate
Sheets("Rough").Cells(i, 2).Resize(1, 3).Select
Selection.Interior.Color = vbRed
Selection.Copy Destination:=ws.Cells(lastr, 1)

Sheets("User provided Input").Activate
lastr1 = Sheets("User provided Input").UsedRange.Rows.Count
Range("a1:a" & lastr1).CurrentRegion.Select
Selection.Copy Destination:=ws.Cells(lastr + 1, 1)
lastr = ws.Cells(Rows.Count, 1).End(xlUp).Row
lastr = lastr + 1
Sheets("Rough").Activate
End If
Next i

End Sub

Thanks,
Mrinal

 

[Colin Legg]

Hi Mrinal

That line is outside of your For Each ws .... Next ws loop, so 'ws' does not hold a reference to a worksheet. It only holds a reference to a worksheet inside that loop.