Free Microsoft Excel 2013 Quick Reference

Insert row on multiple sheets

I need to insert a row on multiple adjacent sheets. I need to do it as a group because I have a 3-D summary sheet whose reference must remain aligned; I don't believe I can use For Each ws.

The active cell on each sheet may be different. I want to insert the row at the location of the active cell on the active sheet. Here's what I've done:
Sub sbrInsertRow(avarSheet As Variant)
Dim intRow As Integer
With Selection
*** formatting instructions ***
End With
End Sub
avarSheet is a variant containing an array, which is properly populated with the names of the sheets to which the row should be added. The worksheets are successfully selected as a group. intRow contains the number of the row of the active cell on the active worksheet.

The calling procedure assembles an array of sheets:
Dim avarSheet() As Variant, i As Integer, intSheet As Integer
intSheet = Worksheets.Count - 3
ReDim avarSheet(intSheet) As Variant
For i = 0 To intSheet
    avarSheet(i) = Worksheets(i + 3).Name
sbrInsertRow (avarSheet())
All avarSheets are selected. The active cell in all avarSheets became the
active cell in avarSheet(0) offset by intRow. avarSheet(0) was the only
sheet on which the row was inserted.

My two problems:
1.) How to make the active cell in all avarSheets be intRow?
2.) How to insert the row on all avarSheets simultaneously?

Post your answer or comment

comments powered by Disqus
Hi to all.

Is it possible to delete the same rows on multiple sheets even when the sheet names have changed and will change annually. A macro seems to be specific and only works if the sheet names remain the same.
Thanks in advance for the response.


I need to figure out a way to automatically insert rows and copy data on multiple selected sheets. for example, if i insert a row anywhere on sheet 3, i need that same row inserted in the same location with the cells populated with the same data on sheets 4, 6 and 9.

I would very much appreciate any help anyone could provide.


I have 200 sheets on a workbook if i try selecting all sheets as a group or then even insert a row it takes ages and screen goes white.. Can anyone help me with quicker way with a vba code where by i could insert row on Row 1 & Row 3 on every sheet of workbook. Thanks



Hi everyone,

I am trying to allow people to insert rows on a sheet that I need to password protect. Here is the catch: I only want to be able to allow the users to insert rows in a certain part of the sheet, not anywhere in the sheet. The standard method of selecting the option won't work because of this. Is it possible to do this?



I have incomplete lists of postcode (PC) data on multiple sheets. On a summary sheet I need to lookup each PC on each sheet and insert a new row with that data if it doesn't exist, or add the data to and existing PC row. The summary sheet can't contain duplicates.

I'm trying to create an inspection report that will allow me to create rows on Sheet 2 based on 2 values on Sheet 1.


This sheet will be used to create sheet 2 rows.
B1 will represent how many parts I will be inspecting
B2 will represent how many inspection points per part


A2 will be where B1 gets calculated
B2 will be where B2 gets calculated


I want each part inspected to be represented by a letter and each inspection point a number.
So if I am inspecting 3 parts with 4 inspection points I would generate the following rows on Sheet 2:

Letter is A2 Column
Number is B2 Column


I inserted the EXCEL file for a better understanding of what my set up is.


Thanks in advance,


Hi, I am using Dave Hawley's code to allow grouping/ungrouping on a protected sheet:

    With Me 
        .Protect Password:="eb", UserInterFaceOnly:=True 
        .EnableOutlining = True 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I need to add the option of inserting rows to the protected sheet using this code. I am using an Excel 2007 file. PLEASE HELP!!
Thank you,

Hey, trying to get a macro to perform a function on multiple sheets. The trusty "Recorder" came up with this, but it doesnt quite work.

Sheets(Array("728", "300", "160", "450")).Select
Selection.Replace What:="0", Replacement:=".001", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

Basically want insert a new column then copy/paste Column B to Column C for each sheet in a book. Then just want an All Sheet Find/Replace of whole cells with 0 to .001.

Hi all,

I'm new at this so will probably ask a lot of dumb questions.

I have a workbook with several sheets, where on one sheet a long checklist is given. Based on the results in this checklist (yes, no or not applicable), I want to hide rows on other sheets (rownumbers are all equal, data in first column is equal too).

Right now, on the first of these 'summarizing' sheets I have the following code:

    Application.ScreenUpdating = False 
    If cell.Value = "yes" Then 
        cell.EntireRow.Hidden = True 
    ElseIf cell.Value = "not applicable" Then 
        cell.EntireRow.Hidden = True 
    Else: cell.EntireRow.Hidden = False 
    End If 
Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
this code only works because I copy data from one sheet to the next (column c on this sheet is equal to column f in the initial sheet). I do not want to keep doing this, since the sheets become filled with useless data. I would like to refer to a range on the initial sheet (column f) but do not know how to make the connection with the 'cell.EntireRow' part

Also, this code takes a lot of time to update the sheet on activation.

Can anybody help?

Hi everyone,

I hope this note finds all doing well. I'm stuck on a macro i'm trying to write for work. Basically, there are 3 sheets that feed into one "Consolidated Sheet". I am trying to have all three sheets show the same amount of rows, based on data in the consolidated sheet.

For example, if in the consolidated tab, rows 1-5 have data, but there is no data in 6-10...I want columns 1-5 in ALL sheets shown, and 6-10 in ALL sheets hidden.

I figured if I just run a simple code, and select all sheets at the beginning of the macro, it'd work on all sheets...this unfortunately is not the case. I'm sure it's a simpleton mistake, but any help would be GREATLY appreciated.

Could anyone please have a look and let me know if theres an obvious problem I can't seem to identify?

Thanks in advance!!

Code is below:

Const strUnitsRange As String = "Roadmap_Hide" 
Const strOrderSheet As String = "Global" 
Const strUnitsRange2 As String = "Action_Plan_Hide" 
Sub HideRows() 
    Dim cel As Range 
    For Each cel In Sheets(strOrderSheet).Range(strUnitsRange).Cells 
        If cel.Value = 0 Then cel.EntireRow.Hidden = True 
        If cel.Value  0 Then cel.EntireRow.Hidden = False 
    Next cel 
    For Each cel In Sheets(strOrderSheet).Range(strUnitsRange2).Cells 
        If cel.Value = 0 Then cel.EntireRow.Hidden = True 
        If cel.Value  0 Then cel.EntireRow.Hidden = False 
    Next cel 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am trying to lock cells on a spreadsheet so that they cannot be changed. Once I do this I find that I cannot insert rows/collumns or hide/unhide parts of the spreadsheet. Is there a way for me to lock certain cells but still be able to change/add rows/collumns to the portion of the spreadsheet that is not locked?



Is there a possibility is i delete a row on sheet 1, for example row 3, I delete row 3 on sheet 2 also?

I need a formula to sum values in the same column on multiple sheets
if it finds a specified value in the same row of a different column.
SUMIF works fine if I select a single sheet but as soon as I select
multiple sheets it errors and returns #VALUE


I am trying to run the following code on multile sheets in a workbook. so far this code is working fine for one worksheet. Can someone help me modify this so it runs on multiple sheets. There can be more then 1 worksheet in a workbook depending upon data. Thanks for the help in advance.

    Dim lngRow As Long 
    Dim lngCount As Long 
    lngRow = 8 
        lngCount = Range("A" & lngRow).End(xlDown).Row - lngRow 
        Range("F" & lngRow & ":G" & lngRow).FormulaR1C1 = "=PERCENTILE(RC[-3]:R[" & lngCount & "]C[-3], 0.95)" 
        lngRow = Range("A" & lngRow).End(xlDown).Row + 9 
        If lngRow > 65536 Then Exit Do 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines



I have a workbook that has an input sheet and an output sheet. The
operator inserts staff course dates on the input sheet, the output sheet
(protected) uses Edate to give expiry dates - the sheets are 'mirrored'.
the problem is when new staff join or leave. I want to insert a row on
input sheet that also inserts the corresponding row on the protected output
sheet and inserts the formulae (I also want to do the reverse). Dave
McRichie has kindly given the answer to the second part on his website. I
have tried editing macros to do the job, but I don't know how to 'find the
insert point' on input sheet. It would be great if I could assign the code
to a button so that the operator is guided; ie 'add or remove a row' that
would update the output page at the same time. I run a lot of stats with
cell references to the output page, I get nothing if these sheets do not
match up properly. Thanks for your help.

Charlie Brown

I have a spreadsheet with 20+ sheets and alot of formulas. There are a total of 210 ranges that I want to insert rows into. They are spread over the different sheets. Some of the sheets only have 1 range to insert rows into an others have up to 25 ranges to insert rows into. It is taking a very long time for the code to insert the rows into each range. So, I opened the worksheet, disabled macros and tried to insert the rows manually. It is taking a long time to insert the rows just into one section. It takes up to 5 seconds for each range. When I go into another spreadsheet I have with lots of formulas and insert rows there, it is quicker. Does anyone have any idea why it's taking so long to insert rows?

I have an excel workbook that is 145 sheets large. Most of the sheets have
cells with reference points to the first sheet. I need to add a row to EACH
sheet in the same location. Is it possible to add a row to each sheet with
one basic command or do I have to do it on EACH individual sheet?

Is there a way that I can lock more than one row on a sheet?

Morning All

I am wanting to automatically insert a graphic file (jpg) into an excel document, but have it appear on multiple sheets. The closest Macro i could find was from
but that only works for 1 cell on 1 sheet... macro for above is :
Private Sub Worksheet_Calculate()
        Dim oPic As Picture
        Me.Pictures.Visible = False
        With Range("F1")
            For Each oPic In Me.Pictures
                If oPic.Name = .Text Then
                    oPic.Visible = True
                    oPic.Top = .Top
                    oPic.Left = .Left
                    Exit For
                End If
            Next oPic
        End With
    End Sub
Any advise or help you could offer would be greatly appreciated!

Hi everybody.

I tried for a long time, but I couldn't sort out how to insert pictures to multiple sheets on the same workbook, by pressing the button on the first sheet.

I want to press the button on the first sheet and see the pictures (from a folder on my computer) show up on the other sheets. Is it possible on Excel 2010?

Thanks in advance!


I am trying to copy a single row in one sheet (say sheet1) and insert the copied row into every sheet in the workbook at a specified row position (entered in a warning box, see macro below). I have been trying to modify the macro below to do this for me.

This macro will insert a row into a specified spot on every worksheet in the workbook.

Option Explicit 
Sub InsertRowAllSheets() 
     '  Thanks to firefytr for the code that has been adapted into this routine
    Dim cs As String 
    cs = ActiveSheet.Name 
    Dim y As Integer 
    y = Application.InputBox("Enter the row number you wish to add", _ 
    Type:=1) 'enter 16 to insert a new row 16, the old row _
    will become 17 And all other rows push down 1 row As well. 
    If MsgBox("Are you sure you wish to insert at row " & y & " for ALL sheets?", _ 
    vbYesNo, "Insert row on ALL Sheets") = vbNo Then Exit Sub 
    Application.ScreenUpdating = False 
    Dim r As Range 
    Dim ws As Worksheet 
     '    On Error Resume Next 'Error handler
    For Each ws In ThisWorkbook.Worksheets 
        Set r = ActiveSheet.Range("A" & y) 
        If y < 7 Then Goto circumv 'Not to insert in Headers
        Range("A" & y).EntireRow.Insert 
         '  code can be inserted here to copy formulas for some or all sheets in the workbook
    Next ws 
    Application.ScreenUpdating = True 
End Sub
PS: If it can be done so that very specific worksheets do not get the row inserted would be nice but not a big problem if that can't be done.

Thank you!

I am working on a database with Names and Volunteer Hours worked over about five years.

What I have is one sheet with names and info, and the respective sheets for each year and different events they worked that year.

on each year sheet I have only the first and last name, which is all that I need for personal info. I have these names on the seet using the sheet1!A1 formula. However whenever I add someone new they will not update the new row on the other sheets, they will simply ignore it. How can I make it respect my row updates?

Thanks for your help!

I'm using Excel 2003 and am having a problem locking rows across
multiple sheets. For example, I have two separate sheets in the same
workbook to track client jobs. Sheet 1 contains job status by job while
sheet 2 contains additional related job information by month. Column
one of sheet 2 references column one of sheet 1. However, all of the
other data in both sheets is independent.

My problem is that when I sort sheet 1 I would like the data in sheet 2
to sort as well, but maintain the integrity of the rows. If Job Alpha
is in row one in Sheet 1, then, after sorting, Job Alpha ends up in row
five, the reference to Job Alpha in column one of sheet 2 will be in
row five as well. However, the data corresponding to Job Alpha that was
in the other columns of row one on sheet 2 will still be in row one.
That is, the entire row will not sort together when one value is

Is there a way to lock the rows in sheet two so they sort together when
sorted in sheet 1?
Sorry for the long and confusing explanation. Thanks in advance...

I need to be able to track changes on selected ranges on multiple sheets, but Excel does not appear to be able to do this. It only appears to allow me to select multiple ranges on the same sheet.

Does anyone know if there a way to track changes on multiple selected ranges on multiple sheets?

No luck finding an answer? You could always try Google.