将Excel工作表复制到另一本没有公式的Excel书中 [英] Copy Excel sheet to another excel book without formulas

问题描述

我使用的VBA代码涵盖了将我的所有工作簿（包括数据透视表和公式）转换为值的所有必备条件。

I'm using this VBA code that covers all my requisites to transform all my workbook, including pivot tables and formulas into values.

Option Explicit
 Sub Copia()
    Dim ws As Worksheet, pvt As PivotTable, aWs As Worksheet, lst As ListObject

    Set aWs = ActiveWorkbook.ActiveSheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            For Each pvt In ws.PivotTables
                With pvt.TableRange2
                    .Copy
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                End With
            Next pvt
            For Each lst In .ListObjects
                If Not lst.AutoFilter Is Nothing Then lst.Range.AutoFilter
            Next
            If .FilterMode Then .ShowAllData
            If .AutoFilterMode Then .AutoFilter.ShowAllData
            .UsedRange.Value = .UsedRange.Value
            .Activate: .Cells(1, 1).Select
        End With
    Next
    aWs.Activate
    Application.CutCopyMode = False

    End Sub

我该如何调整它以仅将我的活动工作表或特定工作表复制到新工作簿？

How can I adapt it to copy only my active sheet or a specific sheet into a new workbook?

谢谢！

MD

推荐答案

您最好生成一个工作表处理子，以将工作表传递给

you'd better generate a single worksheet handling sub to pass a worksheet to

，如下所示：

Option Explicit    

Sub CopiaWS(ws As Worksheet)
    Dim pvt As PivotTable, aWs As Worksheet, lst As ListObject

    Set aWs = ActiveWorkbook.ActiveSheet
    With ws
        For Each pvt In ws.PivotTables
            With pvt.TableRange2
                .Copy
                .PasteSpecial xlPasteValues
                .PasteSpecial xlPasteFormats
                Application.CutCopyMode = False
            End With
        Next pvt
        For Each lst In .ListObjects
            If Not lst.AutoFilter Is Nothing Then lst.Range.AutoFilter
        Next
        If .FilterMode Then .ShowAllData
        If .AutoFilterMode Then .AutoFilter.ShowAllData
        .UsedRange.Value = .UsedRange.Value
        .Activate: .Cells(1, 1).Select
    End With
    aWs.Activate
End Sub

这样您就可以分离代码来处理特定任务因此：

this way you have separated code to handle e specific task thus:

• 对主代码进行整理，使变量对主任务不必要

• uncluttering main code with variable unnecessary to the main task

使您的代码更具可维护性和可调试性

making your code more maintainable and debuggable

事实上，您现在可以让您的主要代码专注于处理单个工作表或单个工作表，而不必担心您已经（希望）稳定且健壮的子程序中编码的过程详细信息。

in fact you now can have your main code concentrate on processing a single worksheet or a single one, without worrying of the process details you coded away in an already (hopefully) stable and robust Sub:

Sub Main()

    ' other "main" code

    CopiaWS Worksheets("MySheetName") '<--| process a single worksheet

    ' other "main" code

End Sub


Sub MainAll()
    Dim ws As Worksheet

    ' other "main" code

    For Each ws In ActiveWorkbook.Worksheets
        CopiaWS ws '<--| process current loop worksheet
    Next ws

    ' other "main" code

End Sub

这篇关于将Excel工作表复制到另一本没有公式的Excel书中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！