using a macro to copy a worksheet but change formulas cell references

[Jamidd1]

Hello excel forum!

I was wondering if it was possible to create a macro that copies a workshee but changes the formulas cell reference. For example moves the cell reference from cell A:1 to A:12 then when run again from A:12 to A:23 etc etc.

Please let me know if you would like me to post a template
Thanks
James

[xladept]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Jamidd1]

Im not sure why but i am unable to attach a file.

When the paperclip icon is clicked nothing happens.

[Jamidd1]

Please see attached.

I am looking to copy the last worksheet named Student 3 and then move the copy sheet to the end and then i want to move the row values (in the formulas) in the freezed pane down by 5, for example if it was B:1 before it was copied they would change to B:6 and the row values in the formulas not in the freeze panes i would like them to move down by 11, for example if the row in the formula was B:11 before it was copied i would like it to change to B:22.

Thanks i hope this makes sense.

I look forward to your reply.

[xladept]

Hi Jamidd,

The code generates a name conflict with sheet for Student 5 - we need to update the names also when we add a new sheet - I need some guidance on that! But, this code will make a new sheet - but, before another is made we'll have to deal with the name assignments

Please Login or Register  to view this content.

[Jamidd1]

Thank you for your help.

Can I not just rename the sheet nyself once it is created a bit annoying but wouldn't be a problem.

Or do you mean something else?

[xladept]

Hi Jamidd,

The workbook names get duplicated with the creation of the new sheet and by the second new sheet there are conflicts - I suggest using this code I found on datawright.com

Please Login or Register  to view this content.

If we could also delete the remaining StudentLinks, then the problem would resolve.

I've come up with this so far - but it doesn't seem satisfactory:

Please Login or Register  to view this content.

[Jamidd1]

Hello Thank you for your reply!

So what do each of these codes do exactly?

[Jamidd1]

I have just tried the last code you entered it does copy the worksheet a treat but it does not do the main bit of changing the row values in the formulas!

Can i ask what the first code you posted in your last message was for?

[xladept]

It does the "main bit" for me! Are your sheet names different than in the sample? Here is the commented code:

Please Login or Register  to view this content.

[Jamidd1]

Wow! thats amazing thank you!

Yes the names will change, will this be a problem?

Would you be able to highlight which bits i would be able to change an adapt to use for other workbooks? I have another workbook very similar just with different tab names and the cell may be different references but the same jump in distant i need when a new sheet is create. it would be nice to have a go at adapting this myself

I can kind of see certain bits but not all!

[xladept]

Changing the names will not only obviate this code but will totally foul up your named ranges - how will the names change? The salient code? Let me know how the names change

*The code that parses the formula is the nitty gritty of this routine

[Jamidd1]

As is student name may change to john smith. They are not going to be student 1 etc.

I already have another macro that names my tab from whats in cell A:1 of the worksheet.

The tabs are all the same in terms of the student details tab and the exam tab etc but the student tabs names will change.

So i would not be able to adapt this code myself?

[xladept]

You'd better send me a new sample so I can, maybe, figure it out The code now is depending on the number in the name

[Jamidd1]

Ahh i see that makes sense! Ok it may take me till the morning ill have to take data out etc.

Thank you for all your help so far!

[Jamidd1]

PS. Maybe we need it to just give it a default name.

[xladept]

Okay -hasta manana!

[Jamidd1]

HelLo!
I have managed to sort out the spreadsheet for you! i hope everything you need is on the workbook. It is very similar to the last one with only some slight changes.

Please see Attached.

Thanks

I really do appreciate your help!

[xladept]

This works after I deleted the StudentLink:

Please Login or Register  to view this content.

[Jamidd1]

Thank you so much this is amazing!! is it better to copy this code into its own module or onto the whole workbook or just one sheet?

I also have another question regarding the copy rows macro you helped me with before if you dont mind helping?

[xladept]

You're welcome!

Any standard module will do but don't use a class module (Sheet or Book).

(If you squelch those hyperlinks, we can write a double-click event to get the sheets.)

What's your question?

[Jamidd1]

Sweet!
and which hyperlinks are you talking about?

[xladept]

I added a line since you're agreeable:

Please Login or Register  to view this content.

[Jamidd1]

I dont understand what this line does?

You havernt explained what i need to do with the hyperlinks haha!

[xladept]

Hey Jamidd,

It does exactly what it says - it deletes all the hyperlinks in the new sheet

[Jamidd1]

Oooo i didnt realise there was any hyperlinks in the new sheets!!

[xladept]

Yeah, that was the glitch after I got rid of the student links and that's why I wrote this bypass code:

Please Login or Register  to view this content.

[Jamidd1]

oo i see i see! So i guess this is solved then!!!

[xladept]

Excellent!

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