Free Microsoft Excel 2013 Quick Reference

Creating new row in locked worksheet

I have a worksheet that is locked for editting for general users but I need these users to be able to use a macro that will add in another project to the sheet.

The workbook is attached.

OP Number
Project Name
PO Number
Service Delivery Manager
TSA / Cost Integrator

All need to be inputted and the formulae which are in columns K:O need to be replicated.

Ideally the user will run the macro and be prompted for the above information then the extra row automatically added in after row 10 or 11 and included in the formulae currently in rows 13 and 14.

Is this possible? Can someone help?

Post your answer or comment

comments powered by Disqus
i'm here again and firstly I would like to thank all the people on the forum
for the help so far. I have learnt a great deal already and managed to
actualy fix some of the issues i asked for myslef.

However I am at a loss as to were to start with this one. I have trawled the
forums but havent a clue so any help as always gratefully received.

I have a workbook with a number of worksheets which contain different peices
of information.
sheet 1 looks like this
A b c d
1 Ref R/A Cust Problem Description
2 12321 Red xxxxxx xxxxxxxxx
3 21212 Amber xxxxxx xxxxxxxxxxxx

Sheet 2 is specific to Amber issues and looks like this
A b c d e f
g h
1 ref Cust Owner Start end No Days Open May Jun
2 21212 xxxxxx aaaaa 04/05/06 05/05/06 1 1 0

Sheet 3 is the same as 2 but for Red issues
The data in F, G, H....etc is all calaculated from formulas in the cells
from the start and end dates

the problem

When i create a new entry in sheet 1, I want to be able to create a new row
in either sheet 2 or 3 dependent the issue colour. I want it to have the
formulas added as well so that is calculates all the relevant data.
In essence i want it insert a row above the last 1. Copy all the formulas to
the new row from the one below and copy the ref number from sheet 1.


Can any one help... please

Rgds Paul

I have a spreadsheet with consistant formulas in each row tracking employee information. This information is linked to another worksheet. I want to be able to insert a new row (employee at any time) that automatically contains the same formula, and the "second" worksheet to automatically link to the new information as well (would have to create a new row in this worksheet too). Is this possible?

Also, I want to protect the formulas in the worksheet ONLY. I have "protected" the sheets, but it doesn't allow any new information to be added. Can I specify formulas only?

I'd be most grateful for any help.

Hi All,

I was wondering if someone could help me write a marco that creates a new row in the below table with the relevant fields in each cell. This is in Word 2007.

Start Date
End Date
Start Time
End Time
No of Staff
Job Role

Please let me know if anyone can help me with this problem.

If you need further information then just ask.

Thanks in advance.



I apologize if this question has been answered before.

I have a Workbook containing five worksheets. Sheets 2-5 feed into sheet 1, which is the consolidated view. I know how to link the sheets with static info, but how can I make it so adding a new row in sheets 2-5 will show up in sheet 1 (in the right place), without having to manually link the new row each time? I want to avoid linking empty cells for later use.

Thanks in advance for any assistance.

Hi, I need to copy across data that is entered into 20 cells in one worksheet into a new row in another worksheet on the click of a button, which then clears the data of said cells so new data can be input, new data that is input is then added into the next row down of the other worksheet as i dont want to loose the previous data but am confused as to how to do this. please could someone give me some advice as i am not very good at excel or macro's.

Thanks :-)

Here is the file that i'm working on it may help give an idea of what i'm trying to do incase i have not explained it properly.


I was wondering if anyone might be able to help me here.

How do i insert a new row in a worksheet in excel using VB programming?

For example, in a worksheet called Data, the following is the data

1 Merchandise 500 600
2 02-000 200 200
3 02-001 300 600
4 Total 1000 1400
5 Grocery 200 80
6 03-001 200 200
7 ...

I'd like to be able to insert a new row after "Total" which is after line no. 4, just to separate them between merchandise and grocery (formatting issue).

I've got some codes but not working really yet (got some errors)

If insertnewline = True Then
Selection.Insert Shift:=xlDown
end if

The error message that i got is
run-time error '1004'
select method of range class failed

I don't know why it doesn't like the range.

Your help is greatly appreciated.

Thank you in advance

Hi folks,

I need a macro that will run from a command button ('New Entry') that will insert a new row into a worksheet. The new row will need to have the following properties:

- must be unprotected so that a user can enter data
- must have the same formating as the previous row (i.e. keep the dropdown lists)

I have attached an example of the excel sheet I will need the macro to run on, the protection password is 'x'.

Total n00b to VBA so all help appreciated,


I've been trying to find this on my own, both by trial and error, on searching this forum and on googling in general, but if I've missed that it's been solved elsewhere I'd appreciate being referred there. On to my question.

I'm seeking a way to update a table with rows constantly as new information is included elsewhere in the same or in another worksheet. This feels like fairly basic Excel, but I'm a newbie and I fail to find how to do it. I want to be able to for example have a sheet of an invoice, that whenever it's updated with new information (a new invoice with the same template, in other words), a summary of that invoice is created as a row in a different worksheet, which sums up all invoices. Another way to create the row would be for example pressing a button, saving the page or whatever.

I'm aware that I can simply make two different tables that are linked together from different worksheets, where one automatically copies the other, but my goal would be to be able to erase the old text in worksheet one, and still have the summary remain in worksheet two.

Is this possible with Excel, or should I be looking for another solution?

I'm thankful for any help or suggestions you might contribute. I tried to be as clear as possible, but please ask if I left something critical out.


Edit: To show where I'm coming from, I've generally worked with SQL before. What I want here (with Excel) would basically be an SQL "INSERT" command. I want to put in the information in the same place several times over, and have it pile up in a static table someplace else.


originally I would like to create a new row on a list on sheet named "List" with each column reference to cells from new sheet I insert. I have a button creating new sheet based on a template which code looks like this:

Private Sub CommandButton1_Click()

Dim strSheetName As String

strSheetName = InputBox("Batch Number", "Please Enter Batch Number")

If StrPtr(strSheetName) = 0 Then
MsgBox ("Job canceled!")
Exit Sub
End If
If strSheetName = "" Then MsgBox ("Batch Number can't be blank! Please enter correct name")
If strSheetName = "" Then Exit Sub

On Error Resume Next
If Err.Number <> 9 Then
MsgBox "Batch number already exists! Please try again"
Exit Sub
End If

Sheets("Batch Number").Visible = True
Sheets("Batch Number").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = strSheetName
Sheets("Batch Number").Visible = False

End Sub

What I would like to add is that information I put in new created sheet cells would appear on sheet named "List" next empty row available. More specific:
From new sheet F8 to list sheet column C
B8 to D
B4 to E
B2 to F
L8 to G
Q15 to H
F25 to I
Q17 to J

I would really appreciate any advise. Thank you in advance!

Hi Need help. Fairly new to macros and VBA.
Looking to create two macro buttons that will cut and paste an entire row based on a selection listed in column N. The copied (or cut) row is to be posted to the next available row in the worksheet matching the selection, and delete the blank row from the source worksheet.

I want the row cut and pasted from the "Funnel" worksheet to the "Dead" worksheet if column N has "Dead" selected, and a similar macro for "Closed" selection to the "Closed" worksheet.


I have been scratching my head over this for several hours now so any help would be greatly appreciated!

I have two worksheets one is for workings the other is for submissions. In order to fill out these spreadsheets I enter a staff number on the workings sheet. This fills in the name, business manager etc on each worksheet. The number of entries on these worksheets differs each month and I am trying to tidy up the unsightly #N/A's that occur when the formula returns no answer. I thought this would be simple but I was wrong.

What I am trying to achieve is that each time I enter the staff number on the worksheet the formula from the row above on both sheets will be copied down onto the next row and the totals row will be moved down by one and include the new row in the formula on both sheets. I have tried to do this with Private Sub Worksheet_Change(ByVal Target As Range) on the working spreadsheet but to no avail! Each time I try all I end up doing is repeating the formula on the first line of my working spreadsheet, having no effect on the submission sheet.

Any help would be greatly appreciated. I would post the code I have been trying but unfortunately in my frustration I have deleted it.

Thanks in advance.

I am relatively new to excel so i am hoping my question is a easy one. I have 2 worksheets. Worksheet 2 references the values in worksheet 1. However when i insert a row in worksheet 1, worksheet 2 will now skip the value of the new row of worksheet 1. How can i get worksheet 2 to update with the new row/s?

I am using the function


Thanks for any help


I am trying to find a way to create new sheets in a workbook that when created will reference a new row on a data sheet in that workbook. Each workbook could end up containing varying numbers of sheets (anywhere from 2 to 150+) based on size limitations. There are some lookups and referenced data on each sheet that would be copied as is and not reference the data sheet. I have not included these in my example.

I'm trying to avoid having to change each cell's reference manually for all of these sheets (which is one of the solutions I've come up with so far). My current method of creating these workbooks does not involve the data sheet and each workbook starts off with data only on sheet1. I select and copy that sheet into sheet2 and every subsequent sheet. I want to incorporate the data sheet so it can be filled out by someone else and copied into that tab to populate all of the data sheets.

I hope I was clear enough in my description. Thank you in advance for the help.


I have a Workbook containing 2 worksheets. Sheet 1 feeds into sheet 2, which is the consolidated view. I know how to link the sheets but how can I make it so adding a new row in the middle of the already existing data in sheet 1 will show up in sheet 2 (in the right place). When i do this right now the cells do not get added in sheet 2 but when i click on cell 9 for example although it is still physically in row 9 the cell info shows cell 10.
Can anyone help? thank you in advance

Hey All,

I am a newbie here, but am not new to VBA. I am trying to complete a large application that I wrote using Excel and VBA for work, but I am stuck on one stupid little thing.

My problem is that I need to be able to determine the number of used rows in a WorkSheet (i.e. rows that contain at least one cell of data). Logically, I thought that the following line of code would get me this result:

dblNumUsedRows = ActiveSheet.UsedRange.Rows.Count 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, this code does not work for me. I have 406 rows containing data on the active sheet, but this code is returning dblNumUsedRows = 12605.


Can anyone help me out? I would greatly appreciate it!


I'm using excel 07, and I need to copy 2 columns from a Dynamic Named Range to new rows in an existing sheet in the same book. I need the inserted rows to copy formulas/format from the rows above, and insert the First and Last names from Sheet1's dynamic named range.

I've been able to somewhat achieve this by vlookup, but I would prefer a macro loop that goes through the dynamic named range and finds new entries and inserts a new row in sheet2.

I have attached the workbook with dummy values. Sheet2 is where all the action happens. To give an idea of the process, a technician exports values from the web into an Excel file. They then "copy" the Last Name & First Name columns from the export file and "paste" them into Sheet1. I need a macro that, when executed, compares Existing Last Name & First Name from Sheet2 to Sheet1 and creates new formatted rows for the new names (the rows will be blank, but will have the same format as the row above).

I've been researching this for days, trying different variations, etc., and have all but given up. I can feel that it is just out of reach, but all of the different function terms are starting to confuse me. I have some experimental sheets in the book, but all I care about are Sheet1 and Sheet2. You can see what Sheet2 should look like, it just needs to be automated.

In the book I attached, there is a new client in Sheet1 that is not in the Sheet2. I have a "manual" insert row macro that keeps the same format, but doesn't compare to the other list to find "new" names.

Also, if Sheet2 could expand and contract as needed by adding rows for new clients on Sheet1, and deleting names no longer on Sheet1, then I wouldn't have to "pre-format" 500 rows. End-user simplicity is preferred, and I will be hiding all sheets other than Sheet1 and Sheet2, so, anything we can do to automate as much as possible is the way to go.

If someone knows how to do it, but is only willing to write this up for a fee, please inbox me with what it would take for you to do the code. I am doing this as part of my internship, so I can't really afford to pay for it. But, if it's the difference between getting the macro and "TLDR", then I'll do what I have to.

Thanks in advance for any advice you can offer. I will check back frequently to see if there are any questions or suggestions.

I am using excel 2003. How would I programmatically create a label in
a worksheet. The following doesn't work:
Dim mylabel As Label
mylabel.Visible = True
mylabel.Caption = "my caption"

Is there a way to programmatically create an array of labels by doing
Dim mylabels(5) as label?

Then could I create/delete the label as needed by using mylabel.create
(or whatever the appropriate method is) and mylabel.delete?

if I want a new function that performs an action on several different
labels can I give it a label/array of labels as a parameter by
declaring the function with something like:
Function my_label_function(changelabel as label) as integer



I am using Excel97 and I try to create a pivot table getting external
data form Ms Access. In Access, my database has over 120,000 records
equivalent to 120,000 rows in Excel. I noticed that Excel has only
65536 rows in each worksheet. How can I increase this maximum rows so
that could I paste the 120,000 records onto the worksheet?


ken2005's Profile:
View this thread:

i have worksheet "1" as my master worksheet with other worksheets linking to
its data. When i instert a new row in worksheet "1" i dont see the new row in
the other worksheets that are linked to can i accomplish inserting
new rows in worksheet "1" so that all other worksheets get the same new row


I need to view the same rows that has a combination of formatted cells
(shaded, merged cells), text, and shapes in mulitple worksheets. Currently,
I am copying and pasting these rows into the mulitple worksheets. I am
looking for a way to do this, so that when I make a changes to these rows, I
can do this in one place, and I am able to view this changes in the mulitple
worksheets - without having to copy and paste in every worksheet. I would
really appreciate your input!



How can I add a row to a work sheet when I reach the last row in my worksheet? The row has many steps and formulas in it.
I would like to have a new row added when ever a new (reqistration number) col a1 is added.

i have worksheet "1" as my master worksheet with other worksheets linking to
its data. When i instert a new row in worksheet "1" i dont see the new row in
the other worksheets that are linked to can i accomplish inserting
new rows in worksheet "1" so that all other worksheets get the same new row


I am using Excel97 and I try to create a pivot table getting external data form Ms Access. In Access, my database has over 120,000 records equivalent to 120,000 rows in Excel. I noticed that Excel has only 65536 rows in each worksheet. How can I increase this maximum rows so that could I paste the 120,000 records onto the worksheet?


Hi all

I use the following code to insert a row of set parameters in one of my worksheets.

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Cancel = True
   ActiveSheet.Rows("10").Copy ActiveCell.Offset(0, 0).EntireRow
End Sub
Currently, if I doubleclick on row 100, the macro copies row 10 to row 99.

I now need to amend this code so that it also performs the same action in a 2nd worksheet; inserting the new row in the same place as the 1st worksheet but using Row 10 from the 2nd worksheet.

I tried using 'goto' with the same macro in the 2nd worksheet but that did'nt work. Hopefully, I have explained this correctly.

TIA ...spellbound

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