How do you stay on the active sheet in active cell?

lbass

Board Regular
Joined
Aug 15, 2002
Messages
104
Every time I make a small step forward I get stumped.

VBA
Finds an account number
looks to the right to see if entry already made
if so moves down a row checks account number
if it is not the same then it inserts a row

Selects sheets so all 57 sheets gets the same row inserted

Active cell is now on the first sheet

no longer on the sheet I need to stay on.

I tried to search but don't know what to search for at least I got no hits.

Thanks you guys are really great.
 
Code:
Sub InsRows()
'/// your code to find and active a cell
'/// then this code to insert rows in ALL worksheets
'/// without having to select each sheet.
    Dim rw
    Dim ws As Worksheet

    rw = (ActiveCell.Row)
    For Each ws In ThisWorkbook.Worksheets
        With ws
            ws.Rows(rw).Insert
        End With
    Next
End Sub


edit: misread original cell selection- took out offset to insert row
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,226,230
Messages
6,189,770
Members
453,568
Latest member
LaTwiglet85

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top