Can't figure it out .... Weird Bug!!

[nathan2314]

Hello.

I can't figure out why this is not working. It was working for a bit but then I added some titles in the first row of a few sheets in the workbook then the code started giving errors. Confusing!
Basically, this part of the VBA code just goes through and clears all the sheets in the workbook from row 2 colume 1 on (so it leave the titles in the first row). So it works fine for the sheet called "Cover Sheet" but then gives a a weird error (The error just says "400") for any of the others that have different sheet names. I've verified that the sheet names in the code match exactly the sheet names in my excel workbook. They do. I changed the logic to Range(A2:AA65536).clear for the Sheet "Protect" and it works fine for the other sheets. But it wont using the range(cells(2,1), Cells(65536,100)).clear for any sheets other that "Cover Sheet" Any idea whats going on????
Here is the snippet of code
Set basebook = ThisWorkbook
'clear all cells on the first sheet
basebook.Sheets("Cover Sheet").Range(Cells(2, 1), Cells(65536, 100)).Clear
'basebook.Sheets("Protect").Range(Cells(2, 1), Cells(65536, 100)).Clear (This won't Work)
basebook.Sheets("Protect").Range("A2:AA65536").Clear (This does work)
basebook.Sheets("Enable ANSF").Range(Cells(2, 1), Cells(65536, 100)).Clear
basebook.Sheets("Socio-Economic Dev").Range(Cells(2, 1), Cells(65536, 50)).Clear
basebook.Sheets("Governance").Range(Cells(2, 1), Cells(65536, 100)).Clear
basebook.Sheets("Measures of Performance").Range(Cells(2, 1), Cells(65536, 50)).Clear
basebook.Sheets("Commander's Assessment").Range(Cells(2, 1), Cells(65536, 50)).Clear

[austenr]

Why don't you post a sample workbook.

[Paul_Hossler]

Using Cells this way applies to the activesheet and "Protect" is probably not the activesheet


'basebook.Sheets("Protect").Range(Cells(2, 1), Cells(65536, 100)).Clear (This won't Work)


Maybe this


basebook.Sheets("Protect").Range(Cells(2, 1).Resize(65536, 100).Clear


Paul

[Kenneth Hobs]

Don't assume that your workbook and worksheet exists.

Sub Test_Clear2toEnd()
Clear2toEnd ThisWorkbook.Name, "Sheet1x"
End Sub
Sub Clear2toEnd(Swb As String, Sst As String)
Dim wb As Workbook
Dim st As Worksheet
On Error Resume Next

Set wb = Workbooks(Swb)
If Err.Number = 9 Then
MsgBox "Workbook, " & Swb & ", does not exist!", vbCritical
Exit Sub
End If

Set st = wb.Worksheets(Sst)
If Err.Number = 9 Then
MsgBox "WorkSheet, " & Sst & ", does not exist!", vbCritical
Exit Sub
End If

Dim lastRow As Long
lastRow = st.Cells.Rows.Count
st.Range(st.Cells(2, "A"), st.Cells(lastRow, "AA")).Clear

Set wb = Nothing
Set st = Nothing
End Sub

[mikerickson]

When using the two argument form of Range, qualification is done on the arguments, not on the function. In
basebook.Sheets("Protect").Range(Cells(2, 1), Cells(65536, 100)).Clear
The Cells are unqualified and refer to the ActiveSheet, not basebook.Sheets("protect")

That could be replaced with
With basebook.Sheets("Protect")
Range(.Cells(2,1), .Cells(65536,100)).Clear
End With
As one can see from my first sentence, I find it useful to think of the two argument form of Range as a function and the one argument form as a Property.