Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

insert a row in a protected sheet

hello out there anyone

does anyone know in excel how to insert a row in a protected sheet without unprotecting the sheet. i do not want any one to delete my formula.

please help

thanks to everyone.

rennoyinc


Post your answer or comment

comments powered by Disqus
While working in a workbook and viewing two sheets in split view I would like to be able to insert a row in sheet 1 without it affecting sheet 3 is this possible in excel 2007?

Nevermind you can if synchronous scrolling is off

Hi all,

If you have several sheets with formulas linked to the main sheet ("Database"). You insert a row into the database to add new information. You then have to insert a row in the linked sheets and then copy & paste the formulas.

Can this be done with a macro?

So for example, on the ("Database"), If I insert a row above "Onions". The macro will insert a row above "Onions" in worksheet ("Veg") and then copy and paste the formulas.

Is that Possible?

Can someone help?

I have a workbook with several sheets, where the first three columns of each sheet are the same, and are supposed to have exactly the same contents.

I have linked all the cells to the ones on the first sheet, but I need to be able to insert a row somewhere in the first sheet, and that that row is automatically inserted in all the ohter sheets in the workbook.

Is this possible? Does anyone know of a VBA script or a macro to do this? I tried doing it with a macro, but it does not do it automatically and also only inserts a specific row as saved in the macro.

Thanks,

Nuno Eça

nuno.eca@clix.pt

whats the keyboard short cut for inserting a row in Excel , deleting a row

Hi,

How can I make one workbook which links to another workbook
automatically create a row, when a row is inserted in the workbook,
which is being linked to.

Furthermore I would like the workbook with the links to automatically
update those links, so they still fit.

This might be a little tricky formulated, so I will make an example:

Workbook A links to Workbook B.

workbook A A1=workbook B A1
workbook A A2=workbook B A2

Quite simple really.

If I insert a row between A1 and A2, I would like workbook A to
automatically (or through a macro) be able to change similarly.

I can make a macro, where I insert a row in both workbooks and update
the formula, but that macro has to insert the row at a given place,
since I dont know how to prompt and make the macro ask for a specific
row.

A macro with a prompt would be fine, but does anyone know how to solve
this one?

Thx. Lars

--
50pingviner
------------------------------------------------------------------------
50pingviner's Profile: http://www.excelforum.com/member.php...o&userid=29298
View this thread: http://www.excelforum.com/showthread...hreadid=495440

Hi,

How can I make one workbook which links to another workbook automatically create a row, when a row is inserted in the workbook, which is being linked to.

Furthermore I would like the workbook with the links to automatically update those links, so they still fit.

This might be a little tricky formulated, so I will make an example:

Workbook A links to Workbook B.

workbook A A1=workbook B A1
workbook A A2=workbook B A2

Quite simple really.

If I insert a row between A1 and A2, I would like workbook A to automatically (or through a macro) be able to change similarly.

I can make a macro, where I insert a row in both workbooks and update the formula, but that macro has to insert the row at a given place, since I dont know how to prompt and make the macro ask for a specific row.

A macro with a prompt would be fine, but does anyone know how to solve this one?

Thx. Lars

How do I reference a row in another sheet in the same workbook?

This problem may sounds funny.
I have two columns say Col. E and F which are protected and contains formulas.
When I try to insert a row, its is throwing error. I want to insert a row and also the formulae in the columns in E and F has to be reflected in the inserted row.
In the meantime the cells in the Col. E and F has to be protected.

Is there any way for doing this?

Thanks in advance.

Rajesh

The below code is what i am using to Save data from the current sheet to a single row in another sheet.
Problem is, sometimes i need to modify the data.
So i use a code run from a button to bring the required data back into the sheet to modify.

Problem is i want to then SAVE the updated data into the SAME row and cell locations as they came from, but the code below will ONLY insert a new row and place the data in that row.
I did have an IF option through the code and use a rngFound bit to try to get the code to only insert an new row IF a value in Column A was not present.

There is ALWAYS a unique Quote number in G5 in the sheet, ant it is to be stored in Column A in the other sheet.

How can i adapt this code to do this?
I have tried all in my knowledge to no avail....

Corey....
Sub StoreSTQData()
Application.ScreenUpdating = False
If Range("G5").Value = "" Then
MsgBox "Please generate a Quote Number before proceeding !!", vbInformation, "Form Name here ...."
Range("G5").Select
Exit Sub
Else
Sheets("Quotes Data").Visible = True
Sheets("Quotes Data").Select
Sheets("Quotes Data").Unprotect
Rows("1:1").Select
Selection.Insert Shift:=xlDown
' Store the data from this sheet into sheets("Quotes data")
Range("A1").Value = Sheets("Quotation").Range("G5").Value
Range("B1").Value = Sheets("Quotation").Range("B1").Value
Range("C1").Value = Sheets("Quotation").Range("B3").Value
Range("D1").Value = Sheets("Quotation").Range("B5").Value
Range("E1").Value = Sheets("Quotation").Range("B7").Value
Range("F1").Value = Sheets("Quotation").Range("B9").Value
Range("G1").Value = Sheets("Quotation").Range("B11").Value
Range("H1").Value = Sheets("Quotation").Range("B13").Value
Range("I1").Value = Sheets("Quotation").Range("B14").Value

Range("J1").Value = Sheets("Quotation").Range("B17").Value
Range("K1").Value = Sheets("Quotation").Range("B19").Value

Range("L1").Value = Sheets("Quotation").Range("A22").Value
Range("M1").Value = Sheets("Quotation").Range("A28").Value
Range("N1").Value = Sheets("Quotation").Range("A30").Value
Range("O1").Value = Sheets("Quotation").Range("A32").Value

Range("P1").Value = Sheets("Quotation").Range("A35").Value
Range("AA1").Value = Sheets("Quotation").Range("A41").Value
Range("Q1").Value = Sheets("Quotation").Range("H46").Value
Range("R1").Value = Sheets("Quotation").Range("H47").Value
Range("S1").Value = Sheets("Quotation").Range("H48").Value
Range("T1").Value = Sheets("Quotation").Range("H49").Value
Range("U1").Value = Sheets("Quotation").Range("H50").Value
Range("V1").Value = Sheets("Quotation").Range("H53").Value

Range("W1").Value = Sheets("Quotation").Range("B55").Value

Range("X1").Value = Sheets("Quotation").Range("M5").Value
Range("Y1").Value = Sheets("Quotation").Range("N5").Value
Range("Z1").Value = Sheets("Quotation").Range("L1").Value
Sheets("Quotes Data").protect
Sheets("Quotes Data").Visible = False
' Clear the data from the Quotes Sheet
With Sheets("Quotation")
.Select
.Unprotect
.Range("G5").Value = ""
.Range("B1").Value = ""
.Range("L1").Value = ""
.Range("B3").Value = ""
.Range("B5").Value = ""
.Range("M5").Value = ""
.Range("N5").Value = ""
.Range("B7").Value = ""
.Range("B9").Value = ""
.Range("B11").Value = ""
.Range("B13").Value = ""
.Range("B14").Value = ""
.Range("B17").Value = ""
.Range("B19").Value = ""
.Range("A22").Value = ""
.Range("A28").Value = ""
.Range("A30").Value = ""
.Range("A32").Value = ""
.Range("A35").Value = ""
.Range("A41").Value = ""
.Range("H46").Value = ""
.Range("H47").Value = ""
.Range("H48").Value = ""
.Range("H50").Value = ""
.Range("H55").Value = ""
.protect
End With
Call Workbook_Info
' Updates some bits and pieces
Call Macro1
' Does a little more before deactivating sheet
Sheets("Enter-Exit Page").Select
End If
Application.ScreenUpdating = True
End Sub

Hi,

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 
        ws.Activate 
        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
         
circumv: 
    Next ws 
    Sheets(cs).Activate 
    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 have a workbook with 15 identical sheets. I wanted to know if I could insert a row in sheet 1 and have it automatically insert the same row in all the remaining sheets?

Thanks in advance

I want to insert rows after every 5 cells in a excel file that has data of
120 rows. I don’t want to start inserting every single time. Is there a
function to insert a rows in a file at an interval?

Hi

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

Here is what I want to do.

I have a workbook that consists of 15 worksheets. I want to be able to click a button to add a row to sheet one and have it added in the other sheets in the exact same place. To complicate it a bit I want it to add it in the same place on the sheet each time (between rows 10 & 11)

I did find a post on how to add a row to sheet one but it adds multiple rows to sheet two with special colors and formulas. I just need it to work like when you insert a row normally. (format and formulas)

I have 4 worksheets, 1 parent and 3 child forms that have links to data
on the parent worksheet. The rows on the child forms are not a one for
one to the parent rows. ex. data in row 1 of parent spans rows 1 - 10
in child sheet 1, spans rows 1-5 in child sheet 2 and spans rows 1-15
in child sheet 3. Row 2 of parent sheet spans rows 11-15 in child
sheet 2, spans rows 6-10 in child sheet 2 and spans rows 16-20 in child
sheet 3.

Now my client has asked that when they insert a row in the parent
sheet, say in between rows 1 and 2, that child sheet 1 will insert a
row between 10 and 11, insert a row in child sheet 2 between rows 5 and
6, and inserts a row in child sheet 3 between rows 15 and 16. All
while maintaining links to the parent sheet.

Thanks

James

Hi,

I am somewhat new to coding with VBA and I bet there is a very simple solution to this but I couldn't find it.

I am writing a macro that needs to scan my table and depending on some conditions copy a row (make a second copy of this row below it).

My data is formatted as a table (using the 'format as table' button).

I am running my ActiveCell using a loop and checking my condition to determine when I need to do the copying. This works fine, my active cell is moving and I am entering my "if" statement at the proper row.

When my condition is met the problem starts. Here is what I am trying to do:


	VB:
	
ActiveCell.EntireRow.Select 
 
 'copy the row
Selection.Copy 
 
 'insert a new row below
ActiveCell.Offset(1).EntireRow.Insert 
 
 'move the active cell one row lower to the newly inserted row:
Range(ActiveCell.Offset(1, 0)).Activate 
 
 'Paste the copied row
ActiveSheet.Paste 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I get to the stage when I need to insert a row (the third statement)

	VB:
	
ActiveCell.Offset(1).EntireRow.Insert 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I get 'runtime error 1004', telling me the operation is not allowed as I am attempting to shift cells in a table in the worksheet.

Well, this is exactly what I want to do! I want to shift the table one row lower and insert a new row, but I am not allowed.

Any ideas how to do it?

cheers,

Yoav

I need to be able to insert a row after the last row in a table. This new row should have the same format as the last row (i.e. same numer of columns).

I tried what the Excel builtin help suggested and that did not work. I have explored the menus in Excel and have not found anything appropriate.

Is there some way using the menus or a simple keystroke combination to accomplish this task ?

Hello I was wondering if there is a way around this problem.
I have the following code.

Public Sub daily2()

Dim rng As Range, rng2 As Range
Dim sh As Worksheet, sh1 As Worksheet
Dim wsheetname As String
Dim res As Variant
Dim DT As Date, s As String
Dim sname As String
Worksheets("planning").Select
Range("A1").Select
I = 10
For g = 1 To 4
For f = 1 To 5
s = Worksheets("planning").Cells(2, 10).Value

Range("d2:h75").Copy

Worksheets.Add After:=Worksheets(Worksheets.Count)
Set sh1 = ActiveSheet

sh1.Select
ActiveCell.Offset(0, 0).Range("A1").Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False
ActiveWindow.DisplayZeros = False

Worksheets("planning").Select

Range(Cells(2, I), Cells(75, I + 1)).Select

Selection.Copy
sh1.Select

ActiveCell.Offset(0, 5).Range("A1").Select
ActiveSheet.Paste Link:=True

sname = Range("f1").Value
sh1.Name = FORMAT(sname, "ddd") & g

L = L + 1
I = I + 2
Next f
I = I + 4
Next g
Range("1:1").Select
End Sub

if I add a row in worksheets("planning"), I would then have to manually
change

Range("d2:h75").Copy
Range(Cells(2, I), Cells(75, I + 1)).Select

I need to paste as a link

also If I add a row in worksheet("planning") I want the new sheet to
automatically add a row. Is this possible?

thanks in advance

Paul

How can I record a macro to be use in a command button or after a particular cell the data has been enter to insert a row below the last cell with data? I have a table with dates and numbers that is use to plot several graphs and I would like to use a macro to insert a row automatically, so when next time to input a new date and numbers the macro will insert the row just below the last entry.

Dear forum,

In my macro that adds a new invoice sheet, I want to insert a listbox that contains the names of all my customers into this sheet. If I pick a name from this listbox, it is inserted in the sheet together with the address. The list with names+addresses are in a separate sheet.
Most of this is working, I only don't know how to insert a listbox in a sheet. I can't find a command for it in the excel menus (because then I would record a macro whilst inserting a listbox and know what the code looks like).
To be clear: I don't want to show a userform that contains a listbox, I want to insert a listbox in the sheet that will still be there when the macro terminated. This way, the name of the customer can be picked after the new invoice has been created and even be changed using the same listbox, if necessary.

Jilt

I am trying to insert a row of simple data into my worksheet. When I do, I
get the following message:

"This operation is not allowed. The operation is attempting to shift cells
in a list on your worksheet."

I don't understand this. I have created lists from the worksheet but don't
have any now. Why does it think I have a list on my worksheet? And, if I have
one, how do I remove it?

I couldn't find anything on this in the knowledge base and am very
frustrated. Thanks for any help.

Hey all, I have noticed my excel doing something odd. When I insert a row in the middle of a lot of data, it will sometimes copy the formula of an above cell if a formula is present, but sometimes it will not.

Is there a macro I can create that will automatically copy any above formula if it is present when I insert a row. If that is not possible, what would be the coding so that if a value is inserted in Cell(x,1) on the worksheet 'TPA AKA List', then the formula in Cell(x-1,6) is copied down into Cell(x,6) (making sure it changes cell references properly for moving down)

To all:
I have a request if someone has the time to look at it.

A bit o background:
I have 2 worksheets. One is a "Friendly" form (Summary Data) so our secretary can enter our vehicle odometer in a cell with the date and click the post button and it copies the information the second less friendly form (Thanks to those here who help immensely with that). Then the current reading column pulls current reading and the date that it was entered to in the column.

The problem that i'm coming across now is there is some difficulty when it comes to adding or deleting a new vehicle. I need the process to be"Dumbed down" enough so the someone with very little excel knowladge can add a vehicle. I was thinking along the lines of a dialog box that would pop up asking for the following information: Driver, Vehicle Number, Current Reading, and Date. From there the macro would insert a row in both sheets based on vehicle #, Enter the driver and Veh # information, and then copy the code from the Current Reading columns to match the inserted row. Deleting would just ask for the vehicle #, ask for confirmation and on yes delete the row in both sheets associated with that veh #.

If there is a better way of doing this i'm open to ideas...

P.S. The whole sheet consists of 40+ vehicles and covers 2 years of dates so far. I tried to get an clip of what the information usually covers.

Thanks is advance.

Below is a copy of the two workbooks for reference.

******** ******************** ************************************************************************>Microsoft Excel - Odometer Tracking.xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE3F3E4F4E5F5E6F6E7F7E8F8E9F9E10F10E11F11E12F12=
ABCDEF1**Insert*ColumnCurrent*Reading2DriverVeh*#ReadingDateReadingDate3Shop*-*Dump*Truck41**1903725/7/074*42**1349039/17/075Chad*M43**2953889/17/076Shop45**1494989/3/077Darrin46**2797169/10/078Shop47**1897034/30/079Loren51**2428729/10/0710Matt52**2363919/17/0711Aaron54**809049/17/0712Mark*S55**1910759/17/07Summary Data*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

******** ******************** ************************************************************************>Microsoft Excel - Odometer Tracking.xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD2E2F2A3B3A4B4A5B5A6B6A7B7A8B8A9B9A11B11A12B12=
ABCDEF1**Date2DriverVeh*#3/5/20073/12/20073/19/20073/26/20073Shop*-*Dump*Truck41160405160406**4042131290131327*131385Chad*M432823072824822825922842316Shop45146319*146319*7Darrin46270022*270042*8Shop47187771*187820*9Loren5122926122786422825422908010Matt5222964722983923014223034211Aaron5468578*690096991012Mark*S55182494188340183542183800Detailed Data*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Hello gurus,

Can somebody show me how to create a macro/script that would insert a row in a worksheet (Sheet1) and then copy the top row of another worksheet (Sheet2) into the inserted row? The copying needs to triggered manually.

We have a 4 copy pre-printed form that we use a dot matrix to print to it and I made an Excel template where the clerk can enter the data and then just hit print. I am trying to capture the data that the clerk types in and create a database so we'll a searchable computer record.

If inserting the data in a row is not possible, how about exporting a row and appending it on a text file?

Thanks in advance.


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