PDA

View Full Version : Solved: Clear Contents in all worksheets in workbook



prasadksu
03-31-2009, 06:44 PM
Hi everbody,

I have a very basic question. I wanted to clear all the contents in all of the spreadsheets when the excel workbook is opened and I also wanted to show the userform when the workbook opens. I tried doing it but I am was not successful. Can anyone give me the code for doing both simultaneously.

Any help would be greatly appreciated.

Thanks,
Pras

Kenneth Hobs
03-31-2009, 07:01 PM
Do this on a backup copy. Right click the Excel icon left of the File menu, View Code, and paste:
Private Sub Workbook_Open()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Sheets
sht.UsedRange.ClearContents
Next sht
UserForm1.Show
End Sub

joms
03-31-2009, 07:08 PM
hi prasadksu try this: right click on any the sheet name like "sheet1", then click on "view code".. then at project explorer click on "this workbook".. then put this code... :)


Private Sub Workbook_Open()

'clear all workbooks
For Each Sheet In ActiveWorkbook.Sheets

Sheet.Select

With Cells
.ClearContents
.ClearComments
.ClearFormats
End With

Next Sheet

'show user form
UserForm1.Show
End Sub

prasadksu
03-31-2009, 07:18 PM
Perfect :clap::clap::clap::clap::clap::clap: It works..........Thanks a lot Joms, and Kenneth Hobs for ur code.............