Trying to get macro to reference newly copied worksheet

[creuther]

Hello all - I am trying to get my macro to copy one worksheet to a new worksheet, clear a few cells, and rename the sheet to the next consecutive number. I have been able to get the macro to do it properly the first time but when I try to reference the newly created sheet, it references the 1st sheet. What I would like to accomplish is have new sheet #2 reference sheet #1, new sheet #3 reference sheet #2, new sheet #4 reference sheet 3 and so on. These worksheets are in the middle of a workbook with about 10 sheets in it. Any help would be appreciated. This is what I have so far.

Please Login or Register  to view this content.

[JBeaucaire]

It's really easy to mess this up. So please clarify.

The first time this runs it copies BITRUN 1 and places it before Sheet(6) and renames it BITRUN 2. What is Sheet(6) called?

The next time this macro runs, it copies BITRUN 1 and places it before Sheet(6) again? Or should it be before sheet(7) this time? Is the placement variable, too? If so, what is the actual name of the sheet these new sheets should be going before?

This would be much easier to do with sample workbook(s) demonstrating BEFORE/AFTER.

[creuther]

What I would like it to do is add another sheet right after the one it just made and keep doing that for as many sheets as necessary. Each bitrun sheet should be before the data assimilation sheet. Attached is the workbook I am working on. Thankyou for your reply.New Mud Pulse Job Paperwork V1.3.xlsm

[dangelor]

Try...

Please Login or Register  to view this content.

[creuther]

Dude - Wow, that was good! Does exactly what I want. Have a great new year.

[dangelor]

Glad to help! I edited the sub. Had a small error. Recopy the new version.

[creuther]

Actually just did a little playing around with the spreadsheet and it looks like the formula in cell I20 doesnt copy to the newly created sheets. Any ideas?

[dangelor]

See attached file

[creuther]

Looks really good but I would like to get the "end depth" from bitrun 2 to transfer to "start depth" on bitrun 3 and so on
Also the "end inc" from bitrun 1 to transfer to "start inc" on bitrun 2 and so on
And the "end date" from bitrun 1 to transfer to "start date" on bitrun 2 and so on

Once again thankyou very much for your patience and time.

[dangelor]

Try...

Please Login or Register  to view this content.

[creuther]

Absolutely perfect!! I cant believe how much you condensed the code. Man do I have a lot to learn.

Thank you so much!!