Excel Macro stops running as intended when assigned to a button-CodePudding

I have the below Macro. When this runs it essentially copy and pastes ranges on the 'Master Sheet' to elsewhere on the sheet where needed:

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

The macro runs how intended. I want to assign it to a button on a different sheet ('Summary_QC'). When I do, the Macro is ran but for some reason it uses ranges on the 'Summary_QC' sheet rather than the 'Master Sheet', despite me stating With Worksheets("Master Sheet").

Any ideas what my problem may be, and possible solutions?

CodePudding user response：

To add the button;

Go to the sheet you want to put it in
On the top go to Developer tab and click on Insert under the Controls
Select Button from Form Control, it will popup Macro selection
Select 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

CodePudding user response：

add this at the beginning of the code before (with): Worksheets("Master Sheet").activate

and add this at the end of the code before (end sub): Worksheets("Summary_QC").activate