Insert rows/columns that auto add formulas

[bugdout]

hey everyone, I have a very formula filled spreadsheet with ranking involved. How can I add either columns or rows that will auto fill the corresponding formulas or rankings? Also, It is a protected sheet so others cannot mess up the formulas/rankings....

thanks for any help

[Glenn Kennedy]

Pass me my Crystal Ball..... I cannot imagine what your sheet looks like, what your forumulae are, anything.

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

However, nothing on this Forum works quite as well as you might expect. The attachment icon doesn't work!! (Helpful, isn't it!!). Instead, just before you submit your post, click on GO ADVANCED (near the bottom) and then scroll down to Manage Attachments to open the upload window. The relevant instructions are at the top of that screen.

[Glenn Kennedy]

Pass me my Crystal Ball..... I cannot imagine what your sheet looks like, what your forumulae are, anything.

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

However, nothing on this Forum works quite as well as you might expect. The attachment icon doesn't work!! (Helpful, isn't it!!). Instead, just before you submit your post, click on GO ADVANCED (near the bottom) and then scroll down to Manage Attachments to open the upload window. The relevant instructions are at the top of that screen.

[bugdout]

Hi Glenn,

Thanks for responding. See attached sheet. Basically what i would need to do, is insert a row anywhere in the schedule and have the formulas copy into that row. See the orange line (its an inserted row and I need the formulas/ranking info in columns A, E, H, K, N,T, W, X, Z + AB:AH to copy when inserting a row).

[jaslake]

Hi bugdoubt

In your PM you referenced Code you tried to adapt from another Thread. Please include such Code in your File.

[bugdout]

I had to go to the post to put in the file. please see attached. and thanks again for helping.

[jaslake]

Hi bugdout

This Code is in the attached and appears to do as you require. Let me know of issues.

Please Login or Register  to view this content.

[bugdout]

hi jaslake,

Thanks for helping, that works great. the little I know of macros I was able to add in to unprotect then reprotect the sheet. Just one question: if I input a row on the schedule, say row 7, how can I add row 7 to the break schdule as well without having to put in another macro? And thanks again for helping out!!

[bugdout]

jaslake, one other question: can the sheet name be changed to whatever THE active sheet is? I have 5 schedules in the main file, Schedule 1, Schedule 2 etc. all set up the same, so if I push the insert row button on say Schedule 4 it would add the row to that scheudle? and really, thanks for helping

[jaslake]

Hi bugdout

This makes no sense to me...there is no relationship between Line 7 in Schedule 1 and Line 7 in Break Schedule...the Structure of these two worksheets are totally different...please explain...

if I input a row on the schedule, say row 7, how can I add row 7 to the break schedule as well

With regard to this...

if I push the insert row button on say Schedule 4 it would add the row to that schedule?

Assuming the Structure of 5 Schedules (Schedule 1, Schedule 2, etc.) is the SAME, simply add a button to each of the 5 Schedules and assign the Macro to that Button.

Change this line if Code from

Please Login or Register  to view this content.

To...

Please Login or Register  to view this content.

[jaslake]

Hi bugdout

I think I understand this issue...

Hi bugdout

This makes no sense to me...there is no relationship between Line 7 in Schedule 1 and Line 7 in Break Schedule...the Structure of these two worksheets are totally different...please explain...

In the attached workbook I've added defined Names to Sheet Break Sced 1:

BS_Cashier

Please Login or Register  to view this content.

and BS_Leardership

Please Login or Register  to view this content.

You'll need to do the same for each of those Categories for which you'd like to add Rows...and for each Break Sced 2, 3, 4 etc.

This Code will add the required Row in the required Sheet and the required Named Range...

Please Login or Register  to view this content.

[bugdout]

hi jaslake, thanks for responding and helping. as far as the issue goes, not sure why, but if there is not the same number of lines in the break schedule as the schedule it doesn't populate correctly.
also, I cannot see where you put the named ranges in the worksheet. I see leadership and cashiers work when a row is inserted, so if I can find where to put the other named ranges in it would work great.
I was able to figure out the active sheet portion of my last qestion, my next would be once I get everything else done, if I am on say sched 4 and insert a row (since the name would be active sheet) would the code know to insert a row in break sched 4? and thanks again for helping me on this.

[jaslake]

Hi bugdout

I don't know HOW it's supposed to populate...perhaps you can demonstrate...

if there is not the same number of lines in the break schedule as the schedule it doesn't populate correctly.

Regarding this...

I cannot see where you put the named ranges in the worksheet

bugdoubt.jpg

[bugdout]

hi jaslake, thanks for letting me know where to put the named ranges in, it through me off when I didn't see them in the attached worksheet. just to confirm, it doesn't matter where I put them in the named range sheet correct? I'll be inputting them today. again thanks for all your help. lots of reputation to you....

[bugdout]

hi jaslake, when I go to insert the BS_Leadership code as above, I keep getting a #VALUE error? any ideas why? I have checked the formatting of the cells etc to trouble shoot, but I cannot figure out why.

[jaslake]

Hi bugdout

The referenced Named Ranges exist in the Workbook I attached to Post #11.

[jaslake]

Hi bugdout

Regarding this...yes...if you've inserted a Button on Schedule 4 and assigned the Code to that Button...
...and if you've added the Defined Names for Break Sced 4 as described.

if I am on say sched 4 and insert a row (since the name would be active sheet) would the code know to insert a row in break sched 4?

[jaslake]

Hi bugdout

If you can't sort this out please attach the offending File.

hi jaslake, when I go to insert the BS_Leadership code as above, I keep getting a #VALUE error? any ideas why? I have checked the formatting of the cells etc to trouble shoot, but I cannot figure out why.

[bugdout]

hi jaslake, I am attaching the downloaded file from your post, I do not see the new rows, and when I try to type in the code I get the #VALUE error. just let me know, and sorry I didn't get back sooner, I had Tuesday/Wednesday off.

[jaslake]

Hi bugdout

Try the File attached to POST #11...Auto Calculate Schedule test v1.xlsm‎...it has the Code to insert a Line into Break Sced.

To find the Defined Names I referenced look in Name Manager...the defined Names need to be added in Name Manager...Understand?

[bugdout]

hi jaslake, i figured out what you where saying, i input the code and the named ranges. when i go to run it, I get a debugger popup, it will add the row to the schedule but not the break schedule? any ideas? i attached the file, so if you can take a look and let me know where I'm going wrong I would be greatful.

[jaslake]

Hi bugdout

You've changed the Sheet Name from Break Sced 1 to Break Sched 1 so you'll need to change the Formula for the Defined Name...

From

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

And you'll need to change this Line of Code to reflect the Sheet Name change...

from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[bugdout]

jaslake, your a genious!!! thank you very much for helping out this. I will put it into the larger file and see if it works.
now, I can use the same code (modified a little) to do the same to take out a row right (for the off season)?

[jaslake]

Hi bugdout

You can use a similar approach to delete a Row; you won't need to deal with Formulas.

You're welcome...glad I could help. Thanks for the Rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[bugdout]

jaslake, will do, just one more final question, my sheet is protected, so when protected it doesn't work.
I know the code is:
ActiveSheet.Unprotect Password:="password"
ActiveSheet.Protect Password:="password". Although I cannot figure out where to put it in to make it work? I have tried at the beginng and end like I have done in several other sheets, yet it doesn't work in this code. any ideas?

[jaslake]

Hi bugdout

Place this Code in the ThisWorkbook Module. Save the File...Close...Reopen...it'll allow the Code to run on protected Sheets.

Please Login or Register  to view this content.

[bugdout]

hi jaslake, can you take a look at the code below, I tried to modify the last one to delete a row on the schedule and also the break schedule. It doesn't work (of course ha ha), it starts the debugger on the Set ws1 = Sheets line of the break sched part. Not sure if the rest of the code will be ok since I can't get past that part. and thanks a gain for all your help, it will not let me give you more reputation until I spread it round appearantly..
Sub DeleteMe()
Dim Ret As Range, A7 As Range

On Error Resume Next
Set Ret = Application.InputBox("Please select the Cells", "Delete Rows", Type:=8)
On Error GoTo 0

ActiveSheet.Unprotect Password:="password"

If Not Ret Is Nothing Then Ret.EntireRow.Delete

ActiveSheet.Protect Password:="password"
Exit Sub

Set ws1 = Sheets("Break Sched " & Right(ws.Name, 1)) 'This is an Existing Worksheet
With ws1
Set Rng = .Range("BS_" & ws.Cells(rCell.Row, "A").Value) 'This is a Defined Name in an Existing Worksheet
With Rng
LR = Rng.Row + Rng.Rows.Count - 1
ws1.Cells(LR + 1, "A").EntireRow.Delete
End With
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True
ActiveSheet.Protect Password:="password"

End Sub

[jaslake]

Hi bugdout

Please wrap any Code you post in Code Tags...it's a Forum requirement.

Post the offending File WITH your Code...I'll look at it.

[bugdout]

jaslake, sorry about that. here is the file.

[jaslake]

Hi bugdout

Please wrap any Code you post in Code Tags...it's a Forum requirement.

In deleting a Row from Schedule 1 will it ALWAYS have a Name in Column B or will it sometimes be blank?

If it has a Name will that Name also ALWAYS appear in Column B of Break Sched 1? (Your Current Data does NOT exhibit this scenario).

Schedule 1
Schedule 1.jpg

Break Sched 1
Break Sched 1.jpg

[bugdout]

hi jaslake, a cell can have a name or be blank on the schedule, but the break schedule should have the same names as the schedule as it pulls the data from the schedule based on the day button at the top of the break schedule. Sometimes I need to add or subtract lines based on number of employees.

[jaslake]

Hi bugdout

Please conform your Sample File to this then I'll look at it.

the break schedule should have the same names as the schedule

[bugdout]

hi jaslake, the break schedule is set for sunday and reflects the schedule for sunday. Just trying to figure out the code to delete the line on the break schedule if I delete one on the schedule. and thanks for helping.

[jaslake]

Hi bugdout

Try this Code in the attached...it's been tested only on Leadership.

Please Login or Register  to view this content.

[bugdout]

hi jaslake, thanks for responding and helping. I opened the file today and enabled it to use it, then I got this error:
Run-time error '91':
Object variable or With block variable not set, I then hit debug and the line:
For each wSht In ActiveWorkBook.Sheets was high lighted yellow. When I exited the debugger and tried the delete row i got this error:
Run-time error '1004'
Delete Method of range class failed. when I hit debug, the line: .Range(rCell.Address).EntireRow.Delete was highlighted yellow. Any ideas? and I truely appreciate your help on this.

[jaslake]

Hi bugdout

If you're referring to the File attached to my post #34 I'm unable to duplicate the error.

This line of Code is in the Workbook Open Event Code which Protects all the Worksheets in the Workbook while allowing Macros to run. It executes fine on my platform.

Please Login or Register  to view this content.

This line of Code is Module 7 in the Sub Delete_Row(). The error would indicate the Sheet is protected and that the Workbook Open Code did not complete (you exited the Debugger). It executes fine in my platform.

Please Login or Register  to view this content.

If you're running the Code on the File from my post #34 (with no changes) then I'm at a loss to explain the error. If you wish you can attach the offending File and I'll look at it

[bugdout]

hi jaslake. the file was the one from your post #34. However, I put the code into my sheet along with your other code for passwords and it works!! so thank you very much... I'm not going to ask you to take a look at the same sheet from post #34, at the refresh/schedule code. Now that I can add or delete a row, I need a VBA code to only delete unprotected cells that have time. It just clears the cells to type over. But if you have some time? and thanks again for helping out, your a true life saver.

[jaslake]

Hi bugdout

Assign this Code to your Button...

Please Login or Register  to view this content.

[bugdout]

hi jaslake, thats perfect. thanks for helping again.

[jaslake]

Hi bugdout

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[bugdout]

hi jaslake, thanks for helping me out previously. i have a new question regarding the same file. for some reason when i try to add a row in the textile area in schedule 4, i get a run time error (only in schedule 4 in textiles). i am stumped as to why only there, i have checked all that i could and am still stumped. can you check out the file and see if you can find why the run time error comes up? and thanks again for helping me out.

[jaslake]

Hi bugdout

Take a CLOSE HARD look at this Formula...

2017-01-22_13-57-08.jpg

[bugdout]

thanks a ton jaslake, i didn't even think to look in the name manager, I kept checking the sheet with all the named ranges. you are the man!!!

[jaslake]

You're welcome...glad I could help.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.