时间戳

首页 > 解决方案 > 将工作表复制到新工作簿并保存

excel - 将工作表复制到新工作簿并保存

问题描述

  1. 复制工作簿 A 中的工作表 1
  2. 创建新工作簿（命名如下）
  3. 将工作表 1 复制到新工作簿中
  4. 将新工作簿另存为 'abc (daily) & Format(Date, "ddmmmyyy") & ".xlsm" - 即代码将以取决于今天日期的方式保存

我不确定我在哪里犯了错误

'Save Worksheet1 as Workbook
Worksheets("Worksheet 1").Activate
With Worksheets("Worksheet 1")
    .copy
End With
saveLocation = "X:\abc\abc\abc (daily)" & Format(Date, "ddmmmyyy") & ".xlsm"

ActiveSheet.ExportAsFixedFormat Type:=xlTypexlsm, _
    Filename:=saveLocation

标签： excelvbacopy

解决方案

Sub CopySheetAsNewWorkbook()

Dim theNewWorkbook As Workbook
Dim currentWorkbook As Workbook

'currentWorkbook is the source workbook, create a new workbook referencing to it with theNewWorkbook
Set currentWorkbook = ActiveWorkbook
Set theNewWorkbook = Workbooks.Add

'do the copy (it's better to check if there is already a 'Worksheet 1' in the new workbook. It it exists delete it or rename it
currentWorkbook.Worksheets("Worksheet 1").Copy before:=theNewWorkbook.Sheets(1)

'Remove default sheets in order to have only the copied sheet inside the new workbook
Application.DisplayAlerts = False
Dim i As Integer
For i = theNewWorkbook.Sheets.Count To 2 Step -1
    theNewWorkbook.Sheets(i).Delete
Next i
Application.DisplayAlerts = True

'Save File as XLSM
saveLocation = "X:\abc\abc\abc (daily)" & Format(Date, "ddmmmyyy") & ".xlsm"
theNewWorkbook.SaveAs Filename:=saveLocation, FileFormat:=XlFileFormat.xlOpenXMLWorkbookMacroEnabled
theNewWorkbook.Close

End Sub

推荐阅读