Clearing Data ranges from Multiple Worksheets

S

Sam Fowler

Hi:

I am trying to clear the contents of certain cells in
several different worksheets at the same time. (Hopefully
with one macro. There are 9 worksheets, with each one
containing 10 pages. the pages are sized exactly the same
and may, or may not all contain data. I have been able to
get the pages to copy and paste to another sheet, but
would like to be able to select all of the pages and
clear data from the data ranges, without clearing my page
formatting, headers, etc. The data ranges are also
exactly the same cells in each worksheet.

Thanks,

Sam
 
V

Vasant Nanavati

I'm confused. Do you mean that you have 9 workBOOKs; each containing 10
workSHEETs?

If so, with only these workbooks open, run a macro such as the following:

Sub ClearDataRanges()
Dim wb As Workbook, ws As Worksheet
For Each wb In Workbooks
For Each ws In wb.Worksheets
ws.Range("A1:Z100").ClearContents
Next
Next
End Sub
 
G

Guest

Hi Vasant:

Actually, it is only 1 workbook. it has 18 different
worksheets. of the 18, 9 of them contain data that I
input. Also, each of the 9 worksheets containing data,
have 10 "Pages". Each page is formatted and also contains
headers, columns for certain calculations, Total etc.,
and are reusable. However, in each worksheet, the data
input range is exactly the same, and I would like to
clear the contents in those cells in one felled swoop.
Cells with data follow this progression:
("A15:L62", "A81:L128","A147:L194",) etc...
After I have copied the data to another sheet, I would
like to empty the data input cells, but not the other
cells on the pages. Also, I only want to perform this on
the 9 specific worksheets. All of the other worksheets
contain different data that I do not want to clear.

Thanks, Sam
 
V

Vasant Nanavati

In that case, try something like this and modify to suit:

Sub ClearDataRanges()
Dim ws As Worksheet, i As Long
For Each ws In Worksheets(Array("Sheet1", "Sheet2", _
"Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9"))
'Substitute your 9 worksheet names above
For i = 0 to 9
ws.Range("A15:L62").Offset(i * 66).ClearContents
Next i
Next
End Sub
 
Top