excel - 仅将可见工作表中的可见单元格复制到新工作簿中，excel 2007 VBA

Question

• 我有一个主电子表格 Master Spreadsheet.xlsm，我想用它来创建另一个由 OutputFN 定义的电子表格。
• 第二个电子表格需要是第一个电子表格的副本，但仅包含第一个电子表格中可见工作表中的可见单元格。

我找到了仅复制可见工作表的代码和仅复制可见单元格但不复制两者的其他代码。任何帮助将不胜感激。

这是我到目前为止所得到的:

Private Sub saveone()

Dim OutputFN As String
Dim OutputWB As Workbook
Dim SourceWB As Workbook
Dim i As Integer

i = 1
Set SourceWB = Application.ActiveWorkbook
OutputFN = ThisWorkbook.Worksheets("Setup Page").Range("B12").Value
Set OutputWB = Workbooks.Add


'Selects active (not hidden cells) from visible sheets and copies

For Each Sheet In ThisWorkbook.Sheets
If Sheet.Visible = True Then
ThisWorkbook.ActiveSheet.Cells. _
SpecialCells(xlCellTypeVisible).Copy

'Pastes into new workbook
Worksheets(i).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats

'Saves new file as output filename in the directory created earlier
 ActiveWorkbook.SaveAs (OutputFN)

i = i + 1
End If
Next

End Sub

Answer 1

类似这样的事情

我整理了变量并稍微调整了逻辑

Private Sub saveone()

Dim OutputFN As String
Dim OutputWB As Workbook
Dim SourceWB As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet    

Set SourceWB = ThisWorkbook
OutputFN = SourceWB.Worksheets("Setup Page").Range("B12").Value
Set OutputWB = Workbooks.Add(1)   

Application.ScreenUpdating = False

For Each ws In SourceWB.Sheets
    If ws.Visible Then
    Set ws2 = OutputWB.Sheets.Add(After:=OutputWB.Sheets(OutputWB.Sheets.Count))
    ws.Cells.SpecialCells(xlCellTypeVisible).Copy
    ws2.[a1].PasteSpecial xlPasteValues
    ws2.[a1].PasteSpecial xlPasteFormats
    End If
Next

Application.ScreenUpdating = True
ActiveWorkbook.SaveAs (OutputFN)    

End Sub