nathan2314
10-29-2010, 01:39 PM
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
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