CODEWITHSUNDEEP
excelexcel-2007vba
Chad Portman
Chad Portman

Reputation: 1216

Macro not working right when called through a button

Using Excel 07

Below is the code I currently have working. However when I assign it to a code the section where it clear contents does not run but everything else does. If I just F8 through it clears the contents no problem at all. I am not sure why this is and was hoping you could help.

Sub Save_Session()

Call UnProtectAll

If Worksheets("Drawing").Range("A2").Value = Worksheets("Drawing").Range("I2").Value Then


i = (Worksheets("Drawing").Range("B2").Value * 25) - 24

Worksheets("Drawing").UsedRange.Copy Destination:=Worksheets("Sessions").Range("A" & i)

Worksheets("Drawing").Range("B2").Value = Worksheets("Drawing").Range("B2").Value + 1

    With Sheets("Drawing")
        Range("A2").ClearContents
        Range("C2:C20").ClearContents
        Range("E2:H20").ClearContents
    End With

Else

MsgBox "You do not have all the winners yet!!!"

End If

Sheets("Drawing").Select
Call ProtectAll

End Sub

Upvotes: 0

Views: 1262

Answers (1)

peege
peege

Reputation: 2477

It's probably working when you step through coincidentally because you happen to be on the sheet "Drawing" when running the macro.

To access properties or parameters of an object, you need to use the "." Since you are already declaring what you are trying to access with the WITH statement.

Here is an article from MSDN explaining how the WITH...End statement works In VBA.

With Sheets("Drawing")
    .Range("A2").ClearContents
    .Range("C2:C20").ClearContents
    .Range("E2:H20").ClearContents
End With

Upvotes: 2

Related Questions