clear cells when workbook is opened

W

Wanna Learn

Hello I want to clear some cells everytime the workbook is opened. the
cells are found in a worksheet "Form"
this is the code - does not do anything when I open the workbook thanks
Private Sub Workbook_Open()
If TypeName(SH) = "Form" Then
Range("D7:F7").Select
Selection.ClearContents
Range("C11").Select
Selection.ClearContents
Range("D13").Select
Selection.ClearContents
Range("G13:I13").Select
Selection.ClearContents
Range("D15").Select
Selection.ClearContents
Range("D17").Select
Selection.ClearContents
Range("D19:J19").Select
Selection.ClearContents
Range("D21:I21").Select
Selection.ClearContents
Range("D25").Select
Selection.ClearContents
Range("C29:J29").Select
Selection.ClearContents
Range("C31:J31").Select
Selection.ClearContents
Range("C33:J33").Select
Selection.ClearContents
Range("C35:J35").Select
Selection.ClearContents
Range("C37:J37").Select
Selection.ClearContents
Range("C39:D39").Select
Selection.ClearContents
Range("G39").Select
Selection.ClearContents
Range("J39").Select
Selection.ClearContents
Range("D41").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=21
Range("D56").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-21
Range("D7:F7").Select
End If
End Sub
 
J

Jacob Skaria

Try the below

Private Sub Workbook_Open()
If ActiveSheet.Name = "Form" Then
Range("D7:F7").ClearContents
Range("C11").ClearContents
Range("D13").ClearContents
Range("G13:I13").ClearContents
Range("D15").ClearContents
Range("D17").ClearContents
Range("D19:J19").ClearContents
Range("D21:I21").ClearContents
Range("D25").ClearContents
Range("C29:J29").ClearContents
Range("C31:J31").ClearContents
Range("C33:J33").ClearContents
Range("C35:J35").ClearContents
Range("C37:J37").ClearContents
Range("C39:D39").ClearContents
Range("G39").ClearContents
Range("J39").ClearContents
Range("D41").ClearContents
Range("D56").ClearContents
Range("D7:F7").ClearContents
End If
End Sub
 
J

Jacob Skaria

Try the below

Private Sub Workbook_Open()
If ActiveSheet.Name = "Form" Then
Range("D7:F7").ClearContents
Range("C11").ClearContents
Range("D13").ClearContents
Range("G13:I13").ClearContents
Range("D15").ClearContents
Range("D17").ClearContents
Range("D19:J19").ClearContents
Range("D21:I21").ClearContents
Range("D25").ClearContents
Range("C29:J29").ClearContents
Range("C31:J31").ClearContents
Range("C33:J33").ClearContents
Range("C35:J35").ClearContents
Range("C37:J37").ClearContents
Range("C39:D39").ClearContents
Range("G39").ClearContents
Range("J39").ClearContents
Range("D41").ClearContents
Range("D56").ClearContents
Range("D7:F7").ClearContents
End If
End Sub
 
J

Jacob Skaria

If you want these cells to be blank; even if "Form" is not the active sheet
try the below

Private Sub Workbook_Open()
With Sheets("sheet1")
..Range("D7:F7").ClearContents
..Range("C11").ClearContents
..Range("D13").ClearContents
..Range("G13:I13").ClearContents
..Range("D15").ClearContents
..Range("D17").ClearContents
..Range("D19:J19").ClearContents
..Range("D21:I21").ClearContents
..Range("D25").ClearContents
..Range("C29:J29").ClearContents
..Range("C31:J31").ClearContents
..Range("C33:J33").ClearContents
..Range("C35:J35").ClearContents
..Range("C37:J37").ClearContents
..Range("C39:D39").ClearContents
..Range("G39").ClearContents
..Range("J39").ClearContents
..Range("D41").ClearContents
..Range("D56").ClearContents
..Range("D7:F7").ClearContents
End With
End Sub
 
J

Jacob Skaria

If you want these cells to be blank; even if "Form" is not the active sheet
try the below

Private Sub Workbook_Open()
With Sheets("sheet1")
..Range("D7:F7").ClearContents
..Range("C11").ClearContents
..Range("D13").ClearContents
..Range("G13:I13").ClearContents
..Range("D15").ClearContents
..Range("D17").ClearContents
..Range("D19:J19").ClearContents
..Range("D21:I21").ClearContents
..Range("D25").ClearContents
..Range("C29:J29").ClearContents
..Range("C31:J31").ClearContents
..Range("C33:J33").ClearContents
..Range("C35:J35").ClearContents
..Range("C37:J37").ClearContents
..Range("C39:D39").ClearContents
..Range("G39").ClearContents
..Range("J39").ClearContents
..Range("D41").ClearContents
..Range("D56").ClearContents
..Range("D7:F7").ClearContents
End With
End Sub
 
J

Jim Thomlinson

The code is event code so you can not just put it into a standard code
module. It must go into thisworkbook. Right click on the XL icon in the upper
left corner of the XL window and select view code to got their directly. The
add something like this...

Private Sub Workbook_Open()
with Worksheets("Form")
.Range("D7:F7").ClearContents
.Range("C11").ClearContents
.Range("D13").ClearContents
.Range("G13:I13").ClearContents
.Range("D15").ClearContents
.Range("D17").ClearContents
.Range("D19:J19").ClearContents
.Range("D21:I21").ClearContents
.Range("D25").ClearContents
.Range("C29:J29").ClearContents
.Range("C31:J31").ClearContents
.Range("C33:J33").ClearContents
.Range("C35:J35").ClearContents
.Range("C37:J37").ClearContents
.Range("C39:D39").ClearContents
.Range("G39").ClearContents
.Range("J39").ClearContents
.Range("D41").ClearContents
.Range("D56").ClearContents
End with
End Sub
 
J

Jim Thomlinson

The code is event code so you can not just put it into a standard code
module. It must go into thisworkbook. Right click on the XL icon in the upper
left corner of the XL window and select view code to got their directly. The
add something like this...

Private Sub Workbook_Open()
with Worksheets("Form")
.Range("D7:F7").ClearContents
.Range("C11").ClearContents
.Range("D13").ClearContents
.Range("G13:I13").ClearContents
.Range("D15").ClearContents
.Range("D17").ClearContents
.Range("D19:J19").ClearContents
.Range("D21:I21").ClearContents
.Range("D25").ClearContents
.Range("C29:J29").ClearContents
.Range("C31:J31").ClearContents
.Range("C33:J33").ClearContents
.Range("C35:J35").ClearContents
.Range("C37:J37").ClearContents
.Range("C39:D39").ClearContents
.Range("G39").ClearContents
.Range("J39").ClearContents
.Range("D41").ClearContents
.Range("D56").ClearContents
End with
End Sub
 
D

Don Guillett

Add more ranges within " " , as desired.

with sheets("Form")
Range("D7:F7,c11,d13,g13:i13").clearcontents
end with
 
D

Don Guillett

Add more ranges within " " , as desired.

with sheets("Form")
Range("D7:F7,c11,d13,g13:i13").clearcontents
end with
 
D

Dave Peterson

But don't forget that leading dot in front of range:

.Range("D7:F7,c11,d13,g13:i13").clearcontents

or drop the with statement:

worksheets("Form").Range("D7:F7,c11,d13,g13:i13").clearcontents
 
D

Dave Peterson

But don't forget that leading dot in front of range:

.Range("D7:F7,c11,d13,g13:i13").clearcontents

or drop the with statement:

worksheets("Form").Range("D7:F7,c11,d13,g13:i13").clearcontents
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Ron de Bruin - Merge cells from all worksheets 0
need help to update macro to office 2007 macro enabled workbook 1
Clear Check Box 2
VBA 2 Codes 2
error with code 4
Create new workbook when macro is run 2
Linking Cells Macro 3
is there a simpler way to do this formula? 5

Top