将工作表从一个工作簿复制到另一个工作簿时出错

Question

在我的 vba 代码中遇到以下错误。

运行时错误"1004"： 工作表类的复制方法失败

Public Sub ExportAsCSV(savePath)
Set ws = Workbooks("OND Estimator").Worksheets("port")  'Sheet to export as CSV
Set wb = Application.Workbooks.Add
ws.Copy Before:=wb.Worksheets(wb.Worksheets.Count)
Application.DisplayAlerts = False                       'Possibly overwrite without asking
wb.SaveAs Filename:=savePath, FileFormat:=xlCSV
Application.DisplayAlerts = True
wb.Close savechanges:=False
End Sub

Answer 1

只需将工作表复制到任何位置即可。这将创建一个新的活动工作簿，其中包含准备另存为 xlCSV 的单个工作表(原始工作表的副本(。

Public Sub ExportAsCSV(savePath)
Workbooks("OND Estimator").Worksheets("port").Copy  'Sheet to export as CSV
Application.DisplayAlerts = False                   'Possibly overwrite without asking
with activeworkbook
.SaveAs Filename:=savePath, FileFormat:=xlCSV
.Close savechanges:=False
end with
Application.DisplayAlerts = True
End Sub

Answer 2

试试

Public Sub ExportAsCSV(savePath)
Application.DisplayAlerts = False    'Possibly overwrite without asking
With Workbooks("OND Estimator").Worksheets("port").Parent
.SaveAs Filename:=savePath, FileFormat:=xlCSV
.Close savechanges:=False
End With
Application.DisplayAlerts = True
End Sub

Answer 3

我发现了这个问题！我没有意识到隐藏工作表会导致复制方法无法正常工作......

Worksheets("port").Visible = xlVeryHidden

。所以我所做的是，我在复制之前暴露了工作表，然后在复制完成后再次隐藏。

Worksheets("port").Visible = True

谢谢你们对我的帮助！