VBA to clear data from worksheets

I have a file that has over 30 different worksheets. Some of these have data I need to keep but the majority have data that needs to be cleared so that we can start collecting the current data and post on the sheets.

Could an expert provide me with VBA code that will clear data from all the sheets in the workbook except for those that I need to keep. There are currently 6 sheets that I need to keep so we can call them Sheet1, Sheet2 etc etc up to Sheet6

Many thanks
JagwarmanAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
Here's a simple example of completely clearing the sheets:

Sub ClearSheets()
Dim wks as Worksheet
for each wks in activeworkbook.worksheets
select case LCase$(wks.name)
' note the sheet names should be listed in lower case!
Case "sheet1", "sheet2", "sheet3", "sheet4","sheet5","sheet6"
' do nothing
Case Else
wks.Usedrange.Clear
End Select
Next
End Sub

Open in new window

0
 
Ivo StoykovCommented:
Hi

You could use this macro and call it for each sheet you want

Sub DeleteSheetContetn()
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
End Sub

Open in new window

HTH
Ivo Stoykov
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Define "clear"? Do you want to keep headers (and if so, which rows), page setup? Are there formulas already set up?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
JagwarmanAuthor Commented:
Hi Phillip

in this case I need to clear the sheets completely headers and all

Regards
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Then both Rory's and Ivo's solutions both work.
0
 
JagwarmanAuthor Commented:
As I need to clear so many sheets Rory's is the better solution for me, but thank you all for your input
0
All Courses

From novice to tech pro — start learning today.