Update a cell value from a previous sheet after copying the sheet and renaming it

This is the code that I have so far.

I have copied a sheet, moved it to the end and renamed it with a date that is in cell "A1"

Now after that process is finished I need it to update the date in cell "A1" of the newly created sheet with the next day's date.

I am stuck however referring to the previous sheet to update the date value in "A1"

Please Login or Register  to view this content.

Perhaps

Please Login or Register  to view this content.

When a sheet is copied by default it becomes the ActiveSheet ... also given the code will by default execute on the ActiveWorkbook there's no need (as I see things) to specify the WB in this instance.

As a habit it's no bad thing to release your objects at the end of your routine (ie Set object = Nothing)

Thanks for your help and the advice I am extremely grateful. Thanks mate.

The new code that you posted works fine once, however when it comes to creating the next sheet in the sequence it fails at this line

Please Login or Register  to view this content.

EDIT: I just realised that you replied to my first post and not the second with the new solution that I had figured out.


So using your code as the solution I updated it change the value in Cell "A1" of the newly created sheet.

Please Login or Register  to view this content.

You should, as a matter of course validate existence of "new sheet name" before trying to name it - ie if 22 Nov 2009 exists already you can't have a further sheet of the same name in the same file.

Don't want to make assumptions here and I apologize in advance if I am in error, but this appears to be a cross-post at Ozgrid, albeit the OP is using a different user name.

Name Worksheet With Date Residing In A Cell

Thanks for the heads up Palmetto, I hate wasting my free time.

Mistweaver, note:

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!