Excel - creating tabs through VBA

[punky415]

Hi. I have a workbook, and individual tabs for each month (this is a budget handling workbook). Is there a way that (through VBA) I can automatically create tabs and name them by month/year as time goes by? For example, I have a tab named "Aug08", and I want the workbook to create a tab named "Sept08" when the current date reaches September 1st? ...

And my second issue (related to the one above): Can this newly created tab be an exact copy of the one before? So I won't have to copy/paste all the info on each tab everytime it is created?

I would appreciate any help. Thank you.

 

[ItIsHardToProgram]

All that you have mentioned above can be done through visual basic, I am not sure though why exactly you would want to create sheets / months considering it will take alot of sheets to cover many years.

I would personaly recommend storing all the rough data in 1 sheet, with a primary key, and using a pivot table to sort the data, or create a form to pull the data.

I will start you on what you are looking for and let you work on it a bit.

in the OnOpen event of the workbook

if Format(currentDate, "MMM") = split(Sheet.Name, "0")

You need to loop through every sheet see if it exists so use a Worksheets.Count to get iSheetCount and loop

For i = 1 to iSheetCount, check every date as mentioned, (make sure your sheet format is exactly the same than your date format)

Once that is done, create a sheet and rename it with the Format(CurrentDate, "MMM") & "08"

This should do it...

Don't hesitate to ask question.

AS I mentioned though, I will not go through the code because you will probably want to change your way of proceeding.

HTH,


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Hi,

This is a recipe for Excel disaster. You will be posting all kinds of questions within several weeks about how to SUMMARIZE your data.

ALL your similar data ought to be STORED in one table (sheet).

From there, you can quite easily REPORT a month's worth (or for a week, a quarter, a year etc) using one of Excel's plethora of data analysis and reporting tools.

But if you insiston going down the road to perdition, check out the Worksheets.Add method. Better yet, turn on your macro recorder and record inserting a sheet.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Second Skip's comment.

I've gone down this road in the past, before I learned better.

It probably seems like too big a pain to restructure everything if it is working right now, but it will, in the long run, save you a whole lot of time.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ItIsHardToProgram]

As ive mentioned in my post, I also recommend restructuring.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.