VBA that copies and inserts new sheet and changes formulas

[Jamidd1]

Hi Excel Forum,

I am using the following VBA to copy a worksheet and inserts a new sheet and changes the formulas so that they move down by 7 and then renames the worksheet with the text value you A1.

I thought i had managed to do it as the formulas seem to move correctly but for some reason the name of the worksheet just does not seem to be correct. It takes the value of another cell instead.

Please Login or Register  to view this content.

I have also attached a workbook as this will probably be more self explanatory then myself trying to explain.

I hope someone is able to assist on this issue!

[jomili]

Jamidd1,
I ran through the beginning of your code. The value for N is 28, so S is looking at B28, which in your file has nothing in it, so S = nothing, so the macro exits at that point.

In your description of your problem you said "and then renames the worksheet with the text value you A1." So, I'm not sure what the problem is. Please clarify.

Please Login or Register  to view this content.

[Jamidd1]

Jamidd1,
I ran through the beginning of your code. The value for N is 28, so S is looking at B28, which in your file has nothing in it, so S = nothing, so the macro exits at that point.

In your description of your problem you said "and then renames the worksheet with the text value you A1." So, I'm not sure what the problem is. Please clarify.

Please Login or Register  to view this content.

If you go to student data and details tab and insert new student rows using the macro button on that tab then go to exam input data tab and use the button to insert rows on that tab. Then change the student name on the student and details tab.
If you then add the new student sheet via the button on the student details. You should then see what the issue is.
The sheet is named summer-16 instead of the new student name.

I hope this helps!
Thank you
Jamidd

[jomili]

Jamidd1,

I followed your directions, and DO see the "Summer-2016" sheet being created. The error occurs because of your N and S values. See the picture attached.

Your formula for determining the values is shown below. When one sheet is inserted, number of sheets is 5, minus 1 is 4, times 7 is 28. So the value from B28 is used for the worksheet name.

Please Login or Register  to view this content.

I think you need to change your formulation. You could do it by looking at the last value in ColumnB, and offsetting 6 rows back. See example below.

Please Login or Register  to view this content.

[Jamidd1]

Thank you very much!

This works a treat! I did think it would be something to do with the calculations i just could not work out where.

Much appreciated.

Jamidd

[jomili]

Glad it helped. If that fixes things don't forget to mark your thread "solved".

[Jamidd1]

Hi Jomili

I have noticed a glitch with looking for the last row then offsetting 6 this means if i insert more then 1 student rows on the student details and data tab, when the new sheet is created it takes the name of the last student added not the 1st of the new students added,

Please see attached work book.

I was wondering if there would be a way to combine all the following 3 VBA so when you click the insert mew student rows it adds the rows on the Student details and data worksheet, Exam data worksheet and then adds the new worksheet for the student aswell?

This would solve the issue and also save time by have to switch between worksheets to add the rows.

Thanks for your help.

Code that adds new worksheet

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Code that inserts rows on Exam data input worksheet

Please Login or Register  to view this content.

[jomili]

I'm surprised you didn't notice MORE glitches. You have two macros with the same name ("InsertRows"), which usually leads to a fault. I don't see a big issue with combining these three, but need to know the order of events. I'm assuming the process starts with your user clicking the "InsertNewStudentRows" button on the "Student Data & Details" tab, which runs your FIRST "InsertRows" macro, then when that process finishes you go to the "Exam Data Input" and click on the "Insert New Student Rows" button, which runs your OTHER "InsertRows" macro, then you go back to the "Student Data & Details" tab and click the "Add new Student Sheet" button. Is that the right order of events?

I'm working on another project right now, but will get back to this as quickly as I can once you clarify.

[Jamidd1]

I thought that would have been a problem but it never seemed to get in the way so i did not get round to changing it if i am honest.

You are correct in thinking that is the process!

Im guessing there will have to be a way for the person to enter the student name at some point so the new student worksheet can be created.

That is absolutely fine whenever you get a chance!

Thank you

Jamidd

[Jamidd1]

I'm surprised you didn't notice MORE glitches. You have two macros with the same name ("InsertRows"), which usually leads to a fault. I don't see a big issue with combining these three, but need to know the order of events. I'm assuming the process starts with your user clicking the "InsertNewStudentRows" button on the "Student Data & Details" tab, which runs your FIRST "InsertRows" macro, then when that process finishes you go to the "Exam Data Input" and click on the "Insert New Student Rows" button, which runs your OTHER "InsertRows" macro, then you go back to the "Student Data & Details" tab and click the "Add new Student Sheet" button. Is that the right order of events?

I'm working on another project right now, but will get back to this as quickly as I can once you clarify.

Hi Jomili,

I was just wondering if you had any chance to look into this yet?

Thanks

Jamidd

[jomili]

Just got finished. Give it a whirl. Per your request, all three macros are now tied together. Two of them I combined into one, and the other ("AddNewSheet") I left alone and just call from the main macro. Main is now called "AddNewStudent", and I've updated your buttons to use it. When you click it it will ask how many students you want to add, then will ask you to input each name. From there it will update the rows, add the sheets, and update the links. Let me know of anything that's not working right.

[Jamidd1]

Thank you very much! This works like a charm as far as i can see!

I was wondering i have my version of this with locked cells etc will this easily paste into my version of this workbook everything is the same apart from some locked cells and some other small changes.

Thanks Again

Jamidd

[jomili]

Locked cells only apply if you protect the worksheet. If you want to keep the worksheets protected all we need to do is write in a routine that unlocks the worksheets at the beginning of the process and relocks them at the end. Easy to do it you need it. See the link: http://analysistabs.com/excel-vba/pr...ct-worksheets/

I'm leaving for the day and won't be back on the computer until late in the morning tomorrow, so don't expect any other replies from me today.

[Jamidd1]

Locked cells only apply if you protect the worksheet. If you want to keep the worksheets protected all we need to do is write in a routine that unlocks the worksheets at the beginning of the process and relocks them at the end. Easy to do it you need it. See the link: http://analysistabs.com/excel-vba/pr...ct-worksheets/

I'm leaving for the day and won't be back on the computer until late in the morning tomorrow, so don't expect any other replies from me today.

Hi jomili,
Thank you for your reply!
I understand how the codes work in the link you provided but cant figure out where I need to put the code to unprotect and then protect again jn this process.

The reason I need this is no formulas are not lost by accident when other users are using the document, so that they can only edit certain cells.
Thanks

Jamidd

[jomili]

See attached. I commented the protection code so you can see where it kicks in and out. Password in the code and in the workbook is 1234; change to whatever you want it to be.

Please Login or Register  to view this content.

I did NOT add any protection to the new Student Sheet. If you want to protect that sheet, you just need to add the protection code after "Get the next Cell" and before "Turn on Events and Quit"

[Jamidd1]

Thanks this is great! Although I have resorted to using the one without the protection!

I have one question regarding Cell A1 on the student sheet that names the worksheet. Is there a way to make that be referenced to the student name in the student details and data tab so if the student name needs to be change on the student details and data worksheet for any reason this changes without me needing to do it manually.

='Student Data & Details'!B10

It does not paste the formula when it finds the students name it just pastes the value.

Sorry for the late reply I have only just had a chance to play around with it.

Thank you for all your help so far.

James

[jomili]

We could do that, but if we did then the student name in A1 wouldn't match the Tab name, so you'd still have to do a manual change to change the tab name.

[Jamidd1]

We could do that, but if we did then the student name in A1 wouldn't match the Tab name, so you'd still have to do a manual change to change the tab name.

That would be fine! If that can be done. Its just incase I need to change a students name for any reason.
Thanks
James

[Jamidd1]

We could do that, but if we did then the student name in A1 wouldn't match the Tab name, so you'd still have to do a manual change to change the tab name.

I have also noticed a glitch when it copies the rows down on the student details and data tab. It puts a random number 23 into the last last cell of the last column for that student.

Instead of copy the formula that is there down

Please see Screenshot

Screen Shot 2016-04-12 at 17.29.52.png

The following formula should be in this cell

Please Login or Register  to view this content.

I thought it may have been an error where i have occidentally changed it by hitting the keyboard but when you change it to the formula and then add a new student the formula gets carried down for the newly added student but the number 23 gets re added to the first student for some reason.

Please see screen shot below

Screen Shot 2016-04-12 at 17.34.56.png