Select Active Sheet doesnt seem to work

Question

So I am trying to write a few macros where I adjust various different bits on a worksheet such as clearing contents and reordering. When the sheet in question "MAIN SHEET" is selected (open on the screen) the macros work fine. When I try to run them from a macro button on another sheet they obviously automatically interact with the active sheet in question.

I have tried using the various different commands for making a sheet active / selecting a range but each time it comes up with Run-time Error 9

Any help on what silly mistake I am making is would be much appreciated!

The current incarnation of one of the codes is below

    Sub sbClearMainSpreadsheet()
Worksheets("MAIN SHEET").Range("A1:X999").Select

Range("A2:F500").ClearContents
Range("J2:O500").ClearContents
Range("R2:Y500").ClearContents
Range("A2:Y500").Interior.Color = xlNone

If MsgBox("Are you sure you want to clear the Main Spreadsheet?", vbYesNo) = vbNo Then Exit Sub

End Sub

thanks

A

Is the worksheet that contains the button in a different workbook to your "MAIN SHEET"? (That's the only reason I can think of that the posted code would give the error you are getting.) — YowE3K, Jun 14 '17 at 16:17
See [Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) for related information — , Jun 14 '17 at 17:37
More at [Sheet / Range Select (combined) not working](https://stackoverflow.com/questions/39792949/sheet-range-select-combined-not-working/39793091#39793091). — , Jun 14 '17 at 17:40

score 2 · Answer 1 · edited Jun 14 '17 at 17:46

2

Instead of Select the "MAIN SHEET", it's better if you use fully qualified objects.

I think you meant your code to look something like the code below:

Sub sbClearMainSpreadsheet()

    ' have the input confirmation box before clearing the contents (not after)
    If MsgBox("Are you sure you want to clear the Main Spreadsheet?", vbYesNo) = vbNo Then Exit Sub

    With Worksheets("MAIN SHEET")
        .Range("A2:F500, J2:O500, R2:Y500").ClearContents
        .Range("A2:Y500").Interior.Color = xlNone
    End With


End Sub

edited Jun 14 '17 at 17:46

answered Jun 14 '17 at 16:17

Shai Rado

33,032
6
29
51

2

Maybe even `.Range("A2:F500, J2:O500, R2:Y500").ClearContents` ...? – Jun 14 '17 at 17:36
@Jeeped that w kid be even better ;) I'm not in front of my PC now, I'll update it later. – Shai Rado Jun 14 '17 at 17:44

score 0 · Answer 2 · answered Jun 14 '17 at 18:47

0

You should use the "Activate" method instead of the "Select" method.

Worksheets("MAIN SHEET").Activate

https://msdn.microsoft.com/en-us/library/office/ff838003.aspx?f=255&MSPPError=-2147217396

answered Jun 14 '17 at 18:47

Graham

7,431
18
59
84

Select Active Sheet doesnt seem to work

2 Answers2