2023

前提条件：

1.模版工作簿中有一张模版工作表，放于路径："C:\Users\pc\Desktop\每周家务安排表11.xlsm" 

2.目标工作簿路径为："C:\Users\pc\Desktop\实例\第7讲-VBA语句(4).Do Loop循环语句.xlsx"

3.实现功能：在目标工作簿中，新建当月天数个模版工作表，并重命名为当月日期

4.代码先实现功能，如果for 循环对性能有影响，后期再改进

代码如下：

 Sub 跨工作簿批量生成模版工作表()

'首先打开模版工作簿和目标工作簿，并将模版工作表复制目标工作簿

Dim wb1 As Workbook, wb2 As Workbook

Dim i As Byte, j As Byte, m As Byte, n As Byte

n = 1

Set wb2 = Application.Workbooks.Open("C:\Users\pc\Desktop\实例\第7讲-VBA语句(4).Do Loop循环语句.xlsx")

i = wb2.Sheets.Count

Set wb1 = Application.Workbooks.Open("C:\Users\pc\Desktop\每周家务安排表11.xlsm")

'新建当月天数个数个工作表

For j = 1 To Day(Application.WorksheetFunction.EoMonth(Date, 0))

wb1.Sheets("每周家务安排表").Copy after:=Workbooks("第7讲-VBA语句(4).Do Loop循环语句.xlsx").Sheets(i)

Next j

'重命名批量新建的模版工作表名称为当月日期

For m = i + 1 To Sheets.Count

Workbooks("第7讲-VBA语句(4).Do Loop循环语句.xlsx").Sheets(m).Name = Format(Application.WorksheetFunction.EoMonth(Date, -1) + n, "mm月dd日")

n = n + 1

Next m

End Sub