分配给不同工作表上按钮的代码使用了不正确的范围

Question

下面的宏将'母版工作表'上的范围复制并粘贴到工作表的其他位置:

Sub Macro2()
With Worksheets("Master Sheet")
Range("CC25:CE33").Select
Selection.Copy
Range("CC44").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("CC21").Select
Selection.Copy
Range("CC40").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("CC6:CE14").Select
Selection.Copy
Range("CC25").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("CC2").Select
Selection.Copy
Range("CC21").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End With 
End Sub 

当我将它分配给不同工作表('Summary_QC')上的按钮时，它使用'Summary_QC'工作表上的范围而不是'主工作表'，尽管声明With Worksheets("Master Sheet")。

Answer 1

添加按钮;

1. 进入你想要放入的工作表
在顶部进入开发人员选项卡，单击控件下的插入在表单控制中选择按钮，会弹出宏选择
2. 选择Macro2

---

Sub Macro2()
Application.ScreenUpdating = False
Dim wb As Workbook
Dim wsMaster As Worksheet, wsCopyTo As Worksheet
Set wsMaster = wb.Sheets("Master Sheet")
wsMaster.Activate
wsMaster.Range("CC25:CE33").Copy
wsCopyTo.Range("CC44").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
wsMaster.Range("CC21").Copy
wsCopyTo.Range("CC40").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

wsMaster.Range("CC6:CE14").Copy
wsCopyTo.Range("CC25").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
wsMaster.Range("CC2").Copy
wsCopyTo.Range("CC21").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
wb.Sheets("Summary_QC").Activate
Application.ScreenUpdating = True
End Sub

Answer 2

将此添加到代码的开头(with):工作表("主Sheet" .activate

并将其添加到代码的末尾(end sub):工作表("Summary_QC" .activate

Answer 3

使用With语句

• ThisWorkbook指包含此代码的工作簿。
• 注意With语句中每行开头的点:它们"告诉"VBA范围位于Master Sheet工作表上。
• 你不需要选择任何东西。
• 删除的PasteSpecial参数是默认值，所以您不需要它们。

Sub Macro2()
With ThisWorkbook.Sheets("Master Sheet")
.Range("CC25:CE33").Copy
.Range("CC44").PasteSpecial xlPasteValuesAndNumberFormats
.Range("CC21").Copy
.Range("CC40").PasteSpecial xlPasteValuesAndNumberFormats
.Range("CC6:CE14").Copy
.Range("CC25").PasteSpecial xlPasteValuesAndNumberFormats
.Range("CC2").Copy
.Range("CC21").PasteSpecial xlPasteValuesAndNumberFormats
End With
Application.CutCopyMode = False
End Sub