Inserting row in sheet (2003)

Topic

New Reply

Hello Everyone,

I need some help. I need to create a macro that will insert a row via a macro or if they put any information on the last row before the total PCA hours sum. I am attaching the spreadsheet for references. Basically I want the PCA hours sum to be moving down as activities are added. Thansk.

Reply | Quote

Replies

Try this version:

Sub ADD_ROW()
Dim lngRow As Long
lngRow = Range(“B65536”).End(xlUp).Row
Rows(lngRow – 4).Copy
Rows(lngRow – 3).Insert
Application.CutCopyMode = False
End Sub

Reply | Quote

Is it possible to have this run with a Worksheet_Change rather than having to run the code after each entry? What I am attempting to do is to have the sum row move down one row each time there is a new entry in the column. The code you have shown requires that I have to run the macro after each enter to get the sum row to move down.

Not sure of syntax for Worksheet_Change.

Thanks.

Reply | Quote

I think it’s not a bad idea to let the user indicate explicitly that a new record is needed. You could put a command button on the worksheet that runs the macro when clicked.

To use Worksheet_Change for this would be complicated.

Reply | Quote

I agree about the button. I just was experimenting with Worksheet_Changes and thought that it might be possible.

On a lighter note, I notice your reply is 23:59. Do you not sleep????

Alos noted that my time was close to the same, but is only 16:00 plus. Assume that this site is running from some place in Europe.

Reply | Quote

Actually, it is past 1 AM here. I usually sleep only 5 hours per night or so.

Our server is in the US, but unless you set your time offset in Edit display preferences, you’ll see UTC aka Greenwich Mean Time under (Adjustable) Time.

Reply | Quote

Any recommendations anyone? I do have a macro code that did something similar, but it assumed that there was nothing below the inserted sheet. Any help would be great?

Reply | Quote

Thanks Hans. I have one problem with the macro above. If the last line has any info on it before the macro is run, it copies it which not what I had intended to do and the sum formula does not carry inserted cells. I still want the user to have the options of selecting facility type and activities in the inserted row. Any ideas or help. Thanks.

Reply | Quote

Does the code I posted not work for you?

Reply | Quote

It does somewhat, but the sum formulas don’t work and it copies the row information above when I run the macro above..

Reply | Quote

Instead of using a macro try the new List feature of Excel 2003:

– Select the range from A5 to the last data cell in column AH. Do *not* include the rows with Total PCA hours and below.
– Select Data | List | Create List.
– Click OK.

When you enter data, a new row will be inserted when necessary, and the formulas will adapt themselves automatically. See what happens when you select a car make in A9 in the attached version.

Reply | Quote

Thank you Hans. This is what I need.

Reply | Quote

Hans,

Is there any way to protect some cells and still get the list feature? I try using the protect option in excel, but everytime I activate it, the option of adding another row for the list is deleted. Any help would be great. Thanks as aways.

Reply | Quote

I don’t think the Excel 2003 List feature works well in a protected worksheet, but I cannot check until tomorrow. Perhaps someone else will have a definitive answer in the meantime.

Reply | Quote

Another Question for anyone. The list function above works, but as it is adding rows the new row format does keep from the one above. Any help would be great. Thanks.

Reply | Quote

Why don’t you want the format of the row above to be copied?

Reply | Quote

Hi Hans. Thanks for the update regarding protecting the document. About the format, If you add a row to the worksheet on this thread, you will see that the border for the added row do not carry to the inserted row. Any ideas.

Reply | Quote

Sorry, can’t test at the moment.

Reply | Quote

As I feared, you won’t be able to add records to a list if the sheet is protected, even if you allow the user to insert rows.

Reply | Quote

Consider exploiting the Data|List|Create List option that your Excel 2003 comes with.

Reply | Quote