Change macro from all sheets to active sheet

[Andy15]

Hi Guys,

I have a macro that inserts rows in a workbook and is applied to all worksheets, this works great but I am trying to modify it to only work on the open worksheet (i am supposing this is activesheet) but it keeps failing.

The code that is working on all the sheets is as follows

Sub InsertRows()Dim ws As Worksheet
Dim found As Long


Application.ScreenUpdating = False


For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Search" Then                  '    "Search" (this is excel name but can be changed by user and break code
            ws.Activate
            
    found = ws.Columns(2).Find(What:="*", After:=ws.Cells(1, 2), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row
    If found < 22 Then ws.Rows(found & ":" & found + (21 - found)).Insert
    
    End If
    
Next ws


Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

My modified code for active sheet is as follows but I keep getting an error
'Run-time error '424':
Object required

I have highlighted the error that is shown in red below

Sub InsertRows_Activesheet()

Dim found As Long


With ActiveSheet


[COLOR=#ff0000]found = .Columns(2).Find(What:="*", After:=ws.Cells(1, 2), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row[/COLOR]
    If found < 22 Then ws.Rows(found & ":" & found + (21 - found)).Insert
    
    End With
MsgBox "Done!"
End Sub

If someone can advise where I am going wrong it would be very much appreciated

Thanks
Andy

 

[lrobbo314]

Probably need to get rid of the ws in this line

[COLOR=#FF0000]After:=ws.Cells(1, 2)[/COLOR]

Try...

[COLOR=#FF0000]After:=.Cells(1, 2)[/COLOR]

 

[Andy15]

Hi lrobbo314

Thanks so much, it worked great.

Feel stupid I overlooked that, but it is great that so many people on here give their time and advice.

Thanks again
Andy

 

[lrobbo314]

No problem. Don't feel dumb. Those moments happen to everyone. Coding has a lot of range in making you feel brilliant as well as giving you those, 'UGH!' moments.