Select a cell in a reset macro

**amotto11** · 05-23-2012, 01:11 PM

Hi All,

I have a Reset Macro here and i am having some trouble with selecting a cell within the macro. If you look at the code i have written a statement on line 22 of something i would like to do. I would like to be clearing the contents of this sheet and right before i clear the contents in cells C37:C56 i would like to select the cell C56. The reason i am doing this is because there is code on that page that conditionally locks cells C37:C55. when i run it right now, since some of the cells in C37:C56 are locked it comes with an error saying it cannot delete things in locked cells. So in order to unlock the cells in the sheet one must delete all of the contents that are the condition on the lock and then for some reason select cell C56. I am hoping that with this order the macro will be able to run the sheets code while runnning the macro and the sheet will be unlocked so that the macro can continue to run. I know this should be something simple but i am a VBA newbie, got most of this code from recording a macro to delete all of the user inputed data. Thanks for any help.

Sub ResetWorkbook2()

'
' ResetWorkbook Macro
' This macro will reset your workbook
'

'
    Application.ScreenUpdating = False
    Range("A8").FormulaR1C1 = "Rate Indication"
    
    With Sheets("Rate Indication Instructions")
        .Range("N28:O53").ClearContents
        .Range("Q37").ClearContents
        .Range("I20").ClearContents
        .Range("I15").ClearContents
        .Range("I14").ClearContents
        .Range("I13").ClearContents
        .Range("I11").ClearContents
        .Range("I10").ClearContents
        .Range("I9").ClearContents
        .Range("I8").ClearContents
        ' This is where I would like to put something like this is .Range("C56").Select but it will not work
        .Range("C37:C56").Cells.ClearContents
    End With
    With Sheets("Loss Development Factors")
        .Range("B3:U22").ClearContents
        .Range("B52").FormulaR1C1 = "=MEDIAN(R[-7]C:R[-2]C)"
        .Range("B52").AutoFill Destination:=.Range("B52:T52"), Type:=xlFillDefault
    End With
    With Sheets("Claim Count - Credibility")
        .Range("B3:U22").ClearContents
    End With
    With Sheets("Summary & Results")
        .Range("P32").FormulaR1C1 = "=R[-4]C"
    'ActiveCell.FormulaR1C1 = "=R[-4]C"
    End With
    With Sheets("Selection")
        .Range("A8").FormulaR1C1 = "Loss Ratio Projection"
    End With
    With Sheets("Loss Ratio Proj Instructions")
        .Range("P22:Q47").ClearContents
        .Range("S31").ClearContents
        .Range("I14").ClearContents
        .Range("I13").ClearContents
        .Range("I11").ClearContents
        .Range("I10").ClearContents
        .Range("I9").ClearContents
        .Range("I8").ClearContents
        .Range("C33:C52").ClearContents
    End With
    With Sheets("Selection")
        .Range("A8").FormulaR1C1 = ""
    End With
    Application.ScreenUpdating = True
End Sub

**tigeravatar** · 05-23-2012, 01:20 PM

amotto11,

You can't select a range unless the worksheet is the active worksheet. so you'd need:

        .Select
        .Range("C56").Select

**amotto11** · 05-23-2012, 01:28 PM

Originally Posted by tigeravatar

amotto11,

You can't select a range unless the worksheet is the active worksheet. so you'd need:

        .Select
        .Range("C56").Select

Okay, That worked. Except now my screen is updating for some reason. I do not want it to update. Any suggestions?

**tigeravatar** · 05-23-2012, 01:29 PM

You could have that worksheet to be the last worksheet that gets reset, and subsequently, the .select and .range.select lines will be basically at the end of the macro. Aside from that, it's weird that the screen updates at all until after the macro turns screenupdating back on.