excel中提取月份_在Excel中自动添加月份表

excel中提取月份

Set up a Master sheet in your workbook, and add month sheets automatically, based on that Master sheet. The new sheets will be named for the month and year, in yyyy_mm format.

在您的工作簿中设置一个母版表，并基于该母版表自动添加月份表。 新工作表将以yyyy_mm格式命名为月份和年份。

自动添加月份表 (Add Month Sheets Automatically)

This video shows the steps for setting up the master sheet and macros, to add month sheets automatically in a workbook. Written details are below the video.

该视频显示了设置主表和宏的步骤，以在工作簿中自动添加月表。 书面详细信息在视频下方。

演示地址

创建一个主表 (Create a Master Sheet)

The first step is to set up a Master sheet in your workbook. In my sample file, the sheet is named wkst_Master.

第一步是在工作簿中设置一个母版表。 在我的示例文件中，工作表名为wkst_Master。

It has a named Excel table, tblSales, starting in cell A1.

它具有一个命名的Excel表tblSales，从单元格A1开始。

主表冻结窗格 (Master Sheet Freeze Pane)

To save time in the new sheets, be sure that the Master sheet is set up exactly the way you want it.

为了节省新表中的时间，请确保完全按照所需的方式设置母表。

In my sample file, there is a Freeze Pane setting, with the first row frozen at the top.

在我的示例文件中，有一个“冻结窗格”设置 ，第一行冻结在顶部。

This keeps the table's heading row visible, when you scroll down on the sheet.

当您在工作表上向下滚动时，这使表格的标题行保持可见。

主表所选单元格 (Master Sheet Selected Cell)

Another setting that I made on the Master sheet was to select cell A1. It's a minor thing, but it saves you the time of selecting that cell  on each new sheet that's created.

我在“母版”表上进行的另一项设置是选择单元格A1。 这是一件小事，但可以节省您在创建的每个新工作表上选择该单元格的时间。

宏来添加月份表 (Macro to Add Month Sheets)

In the sample file, there is a macro to add month sheets automatically, as needed.

在示例文件中，有一个宏可以根据需要自动添加月表。

This macro, named AddMonthWkst, is stored on a regular code module, named modSheets, and does the following steps:

这个宏，名为AddMonthWkst，被存储在一个普通的代码模块 ，名为modSheets，并执行以下操作步骤：

• sets the sheet which will be used as the Master
  设置将用作母版的图纸
• calculates the current year and month name, in yyyy_mm format
  以yyyy_mm格式计算当前的年和月名称
• checks for a sheet with that year/month name
  检查具有该年/月名称的工作表
• if not found, it creates a new sheet, based on Wkst_Master
  如果找不到，它将基于Wkst_Master创建一个新工作表
• names new sheet with current year and month
  用当前年份和月份命名新工作表

宏代码添加月份表 (Macro Code to Add Month Sheets)

Here is the code for the AddMonthWkst macro.

这是AddMonthWkst宏的代码。

NOTE: You can change the master sheet name, and the date formatting, to match what you need in your workbook.

注意 ：您可以更改主工作表名称和日期格式，以匹配您的工作簿中需要的内容。

Sub AddMonthWkst()
Dim ws As Worksheet
Dim wsM As Worksheet
Dim strName As String
Dim bCheck As Boolean

On Error Resume Next
Set wsM = Sheets("Wkst_Master")
strName = Format(Date, "yyyy_mm")
bCheck = Len(Sheets(strName).Name) > 0

If bCheck = False Then
'add new sheet after Instructions
    wsM.Copy After:=Sheets(1)
    ActiveSheet.Name = strName
End If

Set wsM = Nothing
End Sub

工作簿开放代码 (Workbook Open Code)

To make that macro run automatically when the workbook opens, there is code in the ThisWorkbook module too.

为了使该宏在工作簿打开时自动运行， ThisWorkbook模块中也有代码 。

The code is in the Workbook_Open event, and all it does is run the AddMonthWkst macro.

该代码在Workbook_Open事件中，并且所有操作都运行AddMonthWkst宏。

Private Sub Workbook_Open()
    AddMonthWkst
End Sub

测试月度表宏 (Test the Month Sheets Macro)

After you add the Workbook_Open code and the AddMonthWkst macro code to your workbook, close the workbook, and then open it again.

将Workbook_Open代码和AddMonthWkst宏代码添加到您的工作簿后，关闭工作簿，然后再次将其打开。

If a security warning appears, click Enable Content, to allow the macros to run.

如果出现安全警告，请单击“启用内容”以允许宏运行。

Then, if the workbook doesn't already have a sheet for the current month, a new sheet will be automatically added, named with the year and month.

然后，如果工作簿在当月还没有工作表，则会自动添加新的工作表，并以年和月命名。

获取样本文件 (Get the Sample File)

To see how the code works, and add month sheets automatically, get my sample file from my Contextures website.

若要查看代码的工作原理并自动添加月工作表，请从Contextures网站获取示例文件。

Go to the Excel Worksheet Macros page, and get the sample file from the Download section.

转到“ Excel工作表宏”页面 ，并从“下载”部分获取示例文件。

翻译自: https://contexturesblog.com/archives/2019/05/09/add-month-sheets-automatically-in-excel/

excel中提取月份