CODEWITHSUNDEEP
excelvba
rams
rams

Reputation: 1

Select Active Sheet doesnt seem to work

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

Upvotes: 0

Views: 535

Answers (2)

Graham
Graham

Reputation: 7802

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

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33672

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

Upvotes: 2

Related Questions