XLPaste 代码以不同的方式应用于单独的工作表

Question

我昨天问了一个关于这段代码的问题，但这是第二部分。一旦标识的工作表与原始工作簿分离，它们将保留其所有公式(xlPasteValues和NumberFormat)。特别是有两张纸，"REQUESTOR"和"复制"，它们只需要值，但其他 3 张纸需要在分离过程中携带它们的公式。原因是因为这 2 张纸包含外部引用公式，而其他 3 张则不包含。如何让所有工作表分离到新的工作簿中，并使 2 个识别的工作表仅成为值，而其他 3 个工作表将其所有公式带入新工作簿？

Private Sub CommandButton1_Click()

' Plain_Copy Macro
Sheets("PROCUREMENT").Visible = True
Sheets("Request").Visible = True
Sheets("LISTS").Visible = True
Sheets("Copy").Visible = True
Dim TheActiveWindow As Window
Dim TempWindow As Window
Dim ws As Worksheet
With ActiveWorkbook
.Sheets(Array("REQUESTOR", "PROCUREMENT", "Request", "LISTS", "Copy")).Copy
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
.Copy
.PasteSpecial xlPasteValuesAndNumberFormats
End With
TempWindow.Close
Next ws


Application.CutCopyMode = False
Application.ScreenUpdating = True
End With

Answer 1

通过将原始和副本的嵌套With ActiveWorkbook分配给变量，避免任何潜在的问题，然后就可以清楚正在处理哪个变量了。

Option Explicit
Private Sub CommandButton1_Click()

' Plain_Copy Macro
Dim wb As Workbook, wbCopy As Workbook
Dim ws As Worksheet, ar, v
ar = Array("REQUESTOR", "PROCUREMENT", "Request", "LISTS", "Copy")

Application.ScreenUpdating = False
Set wb = ActiveWorkbook
With wb
For Each v In ar
.Sheets(v).Visible = True
Next
.Sheets(ar).Copy
Set wbCopy = ActiveWorkbook
End With

For Each ws In wbCopy.Worksheets
If ws.name = "REQUESTOR" Or ws.name = "Copy" Then
With ws.UsedRange
' replace formula with values
.Value2 = .Value2
End With
End If
Next

Application.ScreenUpdating = True
MsgBox "Copy is " & wbCopy.name 
End Sub