Code assigned to a button on different sheet uses incorrect range

Question

The below macro copies and pastes ranges on the 'Master Sheet' to elsewhere on the sheet:

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

When I assign it to a button on a different sheet ('Summary_QC') it uses ranges on the 'Summary_QC' sheet rather than the 'Master Sheet', despite stating With Worksheets("Master Sheet").

You have a `With` statement, but then are not using it on your `Range` objects.... use `.Range` to make it use the `With` instead of the `ActiveSheet` — braX, Dec 05 '22 at 12:18
Also, think about using other ways of copying data. Select/Copy/Paste is slow and prone to errors, see https://stackoverflow.com/a/10717999/1490783 for alternatives. — Olle Sjögren, Dec 05 '22 at 12:25
@braX Thank you but I had tried this before and it returns an error - 'select method of range class failed'? — tthom, Dec 05 '22 at 12:52
If you have the right worksheet name, are you sure it's referencing the correct workbook too? — braX, Dec 05 '22 at 12:56

Vetuka · Answer 1 · 2022-12-05T13:00:17.063

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

Thanks Vetuka. I should have been more specific. The button is to be on the 'Summary_QC' sheet, but the copy and pasting is to take place all on the 'Master Sheet'. Rather than copying from 'Master Sheet' to 'Summary_QC sheet which I see you code does — tthom, Dec 05 '22 at 12:49
@tthom I updated my answer. This should work like you wanted now. You can also test the code when you run it from Summary_QC sheet. — Vetuka, Dec 05 '22 at 13:00

score 0 · Answer 2 · answered Dec 05 '22 at 13:10

0

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

answered Dec 05 '22 at 13:10

Thabet Sh

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 08 '22 at 18:35

score 0 · Answer 3 · answered Dec 05 '22 at 20:25

Using the With Statement

ThisWorkbook refers to the workbook containing this code.
Note the dots at the beginning of each line in the With statement: they are 'telling' VBA that the ranges are located on the Master Sheet worksheet.
You don't have to select anything.
The removed PasteSpecial parameters were the default values so you don't need them.

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

Code assigned to a button on different sheet uses incorrect range

3 Answers3

Using the With Statement