Create New Sheet when Open (VBA)

[MrVillareal]

Hi MrExcel Users,

I have one workbook 3 sheet name as of now named, DN01102011-1, DN02102011-2, DN03102011-3.. DN is Debit note, 01 and subsequent numbers are weekdays (our week ends in Friday) because we work saturday to thursdays, and 2011 is the year, the remaining numbers (-1,-2,-3) are the number of sheets I have.

What I want to do using VBA is that whenever I open this workbook I want the excel to create new sheet copying the same content (all formats and formulas) of the last sheet which is for this case DN03102011-3 and create DN04102011-4.

Thank you in advance and I appreciate all your helps.

Best regards,
Marvin

 

[GTO]

Greetings Marvin,

I am not sure, but as the added sheet appears to be a new day, we may need to understand the naming convention better. Could you correct or clarify what I have understood?

'  Debit Note   Week day 01-06?    ???     Year     Hyphen    Sheet Number
'       |         Sat = 01           |        |         |          |
'       |         Thu = 06           |        |         |          |
'       |             |              |        |         |          |
'      DN            01             10      2011        -          1

Use the code tags like:
[code] your example, typed in notepad or VBIDE or similar[/code]
as these rebder fixed width.

Mark

 

[Norie]

Marvin

Is it Debit Note (DN) 01 Oct 2011 - 1, Debit Note (DN) 02 Oct 2011 - 2 etc?

Is the date the current date when the workbook is opened?

Is the suffix, eg 1, 2 etc always the same as the day of the date?

Or is it just sequential, eg 1,2,3,4,5...?

 

[MrVillareal]

Hello guyz thank you for your reply. Yes actually those are indeed date. DN is the debit note and 01102011 is actually 01 October 2011 and Hyphen "-" with the number is actually the sequence number..I want excel to automatically create a new worksheet with the current date and make a sequence and copy the content of the last sheet. Because everyday I copy the last sheet to make a new one. HOpe this clarify your queries. Again thank you for your precious time

 

[Norie]

So the number after the hyphen isn't based on the date?

Are you sure you want to copy one of the existing sheets?

Won't they have data?

Wouldn't it be easier perhaps to create a template sheet with all the formulas and formatting but no data and copy that?

 

[MrVillareal]

Yes it is a sequence number after the hyphen. Yes just the format and formulas not necessarily the content because eventually I will be editing the content.