Automatically Add Sheet When Excel Workbook Opens
Tip: Highlight text to annotate itX
In this workbook we have a sheet for each month of the current year
where we put the sales for that month.
When the workbook opens the first time in a new month we'd like it to automatically
insert a worksheet with
the year and the month number for that month.
To do that, we're going to use a macro in Excel VBA
To open the VB Editor, where we can write some code
I'm going to
go to the Developer tab, on the Ribbon,
and click Visual Basic. If you don't
have the Developer tab, when I go to the Visual Basic button,
I can see the shortcut just below
my pointer, where it says Visual Basic Alt + F11 so you can use that
keyboard shortcut instead of
clicking the button.
In the Visual Basic Editor
I can see the workbook
that I'm using right now
and
below that
is a list of all the worksheets in this workbook
and ThisWorkbook
I'm going to insert a new module where I can
put the code that is going to run
to create the worksheet.
So, on the Insert menu
I'll click Module
That creates Module1
and the
cursor is flashing where I can start typing.
I'm going to type
the word Sub
to start the sub procedure
and I'll call this
AddMonthWorksheet When I press Enter
it automatically puts in the End Sub line for me.
Now we want to create a new worksheet if necessary
so I'm going to create a variable for the worksheet so dim
I'll call this ws for worksheet and it will be
defined as worksheet.
We also want to create a name for this so I'm going to create a name variable
str for string
Name as
string.
And finally, we're going to be checking to see if that sheet exists, so
Dim boolean this is going to be true or false
so bCheck as boolean
We want to make sure this code keeps running no matter what, so I'm going to put in
On Error Resume Next
That will just keep the code running, even if it can't find the sheet
that we're checking for.
The name of this sheet,
we want to be equal to the year
underscore month, so to do that we can use Format
and the current date
and then
4 y's underscore and 2 m's yyyy_mm
that will give us the 4 digit year and
2 digit month number
with an underscore between them.
You could use other formats if you prefer. You could use just 4 m's -- mmmm
to get the full month name, if that's what you need.
Then bCheck,
we're going to test to see if there's a worksheet with that name.
And if we're going to use Len, which will check for the length
of the worksheet name.
So then it's going to look in the collection of sheets, see if it can find one with
that string name we just created
and
test the length of that
sheet's name.
And tell us whether or not
that's greater than zero. So if the sheet doesn't exist
that line just won't work,
so the bCheck
will remain as False.
If it can find that sheet, it'll be True.
So then we're going to tell Excel what to do
if bCheck is False.
It didn't find the sheet, so
what should it do? That sheet doesn't exist, so
it will create a new one for us when we write another
couple of lines of code here.
Press Tab,
to indent.
We want to
create a sheet, so Set
using our variable, ws
equal
and then in the work sheets collection
Add
and we'd like it Before
the Sheets(1)
so before the first sheet it's going to add our new sheet
and the next line will give it a name, so for that new worksheet the name is equal to
the name that we created
near the top of this procedure.
And
to finish our If
I type End If
and that's our procedure
that will create a new worksheet when necessary.
To test this, we can click somewhere in this
procedure
and click the run button.
And there, it's added 11 04 for us, because one didn't exist previously.
I can delete that,
just so we can test it later.
Now, back in our code here
I'm going to double-click
the procedure name
and copy it.
And then in the list
of objects, double-click ThisWorkbook.
There's nothing here right now.
I'm going to click this drop down
and select Workbook
and it automatically creates a Workbook_Open event.
Press Tab, to indent, and then paste that procedure name that I copied.
Now I'll Save
this workbook
and close it, so we can
test the code.
So there is no 11-04 worksheet.
I'll open the workbook
and it's automatically created one for us.
