时间戳

首页 > 解决方案 > 复制隐藏的 Excel 工作表

excel - 复制隐藏的 Excel 工作表

问题描述

我正在尝试复制隐藏的 Excel 工作表，但它显示错误“工作表类的复制方法失败”

Workbooks("FCD Alert").Activate
xPath = Application.ActiveWorkbook.Path

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        DisplayAlerts = False
    End With

    Set Sourcewb = ActiveWorkbook
    Sheets("Yesterday").Activate
    'Copy the ActiveSheet to a new workbook
    Sheets("Yesterday").Copy <- This region is getting Highlighted 
    Set Destwb = ActiveWorkbook

    'Determine the Excel version and file extension/format
    With Destwb
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else

标签： excelvba

解决方案

该.Copy方法仅使用复制的工作表创建一个新工作簿。在任何工作簿中必须至少有一个工作表可见（您可以通过尝试创建一个只有 1 个工作表的新工作簿来独立验证这一点，然后尝试隐藏它：

在此处输入图像描述

因此解决方案应该是在复制之前取消隐藏，然后在复制后将其隐藏在源工作簿中。

Set Sourcewb = ActiveWorkbook
Dim yesterday as Worksheet
Dim wsVis as Long
Set yesterday = Sourcewb.Sheets("Yesterday")
wsVis = yesterday.Visible  ' # Get the sheet's visible state
yesterday.Visible = xlSheetVisible  ' # Make it Explicitly visible
'Copy the ActiveSheet to a new workbook
Set Destwb = yesterday.Copy
yesterday.Visible = wsVis ' # return it to its original visible state

推荐阅读