Lock a Row and everything below it

[rlsublime]

If I have data in col A1 to A10, I am trying to get row A11 and everything below to move down automatically as new data is added to A10 onwards. Could i name cell A11 summary row and have it keep moving down somehow? I also want it to account for formulas. for eg if A1 to A10 sums in A11..when new data is added, I want it to auto calculate the new data and move down automatically. I need it to move down automatically because the data is being populated from another sheet.

Is there a way to lock a row and everything below it. I am trying to add summary data to a sheet and have a row and everything below if to keep moving down as new data is added to the sheet. the summary row should also account for new data added in the sum.
Thanks

[royUK]

It would be much simpler to put the summary details above the data.

Also look at using Lists

[arlu1201]

When data is populated from the other sheet, is it done manually or via macro? If its done via macro, we can automatically move the data down through the code itself.

[rlsublime]

It is done through a macro. Here is the code that adds the data from A to U from another sheet. So I was trying to get the data to paste between a summary row and have the summary row automatically move down. Also from V to Z, I manually entered formulas so I was trying to get the formula to show up if there is data in A to U.
Thanks

Please Login or Register  to view this content.

[arlu1201]

Please attach the workbook that uses this code. It will be easier to help you out

[rlsublime]

Here is the Test file I am trying to add a summary row in the Bill tab and all other tabs with other names. I am trying to get the summary row to keep moving down as new data is added while also incorporating the new additions. The red highlighted portion is the manual formulas which i am trying to get to automatically update as well.

Thanks for your help

[arlu1201]

I have edited your code a lil bit (after many rounds of trial & error) to insert a row and then copy the data. This ensures that the formula includes the new row. You can use this code -

Please Login or Register  to view this content.

[royUK]

You've attached an Excel 2007+ workbook, your profile says that you are using Excel 2003. My answers were based on that.

Look at Excels Table feature & amend your profile

[rlsublime]

Thanks for the help! The code worked. One other thing that I was trying to do was have the row area from V to AD also move down. The code ends the range at U6 since that is the data populate from the macro. I have attached a screen shot of the issue, Columns V to AD contain formulas so i am trying to get the formulas to automatically enter and move down as new data is added and also have the final summary rows to offset by the same as A to U..

Thanks again for the help.

[arlu1201]

Your revised code is here -

Please Login or Register  to view this content.

[rlsublime]

Hi Arlette: Thanks. Unfortunately when I used the edited code it did not generate as expected. The V to AD columns disappeared. Is there a way to imbed the formulas in the code since the formulas will be consistent for all sheets in the loop. The only difference in each sheet is the values in the table that they make reference to. the ranges will always remain the same.

Thanks for your help!

[arlu1201]

I tried the code at my end and it works. I used the same spreadsheet as you had attached for your other similar request. If you are using another spreadsheet, please attach it along with the code and i can help you troubleshoot.
Just an observation - on the Data sheet there is just 1 line for Bill, still the code goes from A3:A14 (there is data only in A4). This is a waste of time since there is no data. You can change the code to ask it to loop only for those lines which have data in them. You can change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[rlsublime]

It still did not work for me for some reason.I reattached the file. I am not sure if I pasted the formulas incorrectly in the initial file that I had attached.
Could you possibly reattach the file that you were able to generate?

Thanks for the help

[arlu1201]

I see formulae only for row 6 here. There is no updated formula for row 7 which i have in the workbook. Find attached.

[rlsublime]

Thanks for the file. I figured out what I was doing wrong. I was generating will no data in Bill tab. The macro works perfectly when there are 2 lines of completely entered data, however there are errors when there is no data or only formulas. Do you think there is a way to generate when there is no data in A to U and only formulas from V to AC?


Thanks for the help

[arlu1201]

What do you mean by

Do you think there is a way to generate when there is no data in A to U and only formulas from V to AC?

[rlsublime]

The macro works perfectly when there are two lines of data already in the bill tab, but when there is no data then the macro seems to be getting offset. Is ther a way to have the macro generate when there is no data at all in the bill tab?

Thanks for the help!

[arlu1201]

So in the Bill tab, there will only be the header right? And no data? What about V to AD? Will there be formulae there or nothing but the header?

[rlsublime]

That's correct, Arlette. The ideal scenario would be to have no data in the bill tab besides the headers and the summary row and to also have the formulas automatically pasted in the rows with data. I could possibly put a formula in the first v to ad row so it pulls it to the rest...thanks for your help.

[arlu1201]

I will work something out for you by tomorrow. (Already late night here )

[arlu1201]

It was getting too complicated to alter your existing macro so i changed it entirely. Find below the code which will insert the data into the tab where the name is provided in column A of the Data sheet, then it will insert formulae from V to AC. If the first row of formulae are missing, it will enter that. If the first row is present but 2nd row is missing, it will enter those formulae. Basically, it starts from scratch. So you can ideally blank out everything from the header down.
Also, it will insert the sum row that will sum the values. For now, i have summed certain columns which were there in the file you sent me. If there are new ones to be added, either you can do so or provide it to me. It will delete all summation values after the last row of data and insert again including the newly added rows.
Try the code and let me know.

Please Login or Register  to view this content.

[rlsublime]

Thanks so much that worked perfectly! Really appreciate the help!