Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Data ranges from Multiple Worksheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Data ranges from Multiple Worksheets
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 -- Vasant "Sam Fowler" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Data ranges from Multiple Worksheets
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 -----Original Message----- 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 -- Vasant "Sam Fowler" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Data ranges from Multiple Worksheets
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 -- Vasant wrote in message ... 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 -----Original Message----- 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 -- Vasant "Sam Fowler" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming ranges on multiple worksheets | New Users to Excel | |||
How do i update multiple data ranges across multiple worksheets? | Excel Discussion (Misc queries) | |||
how to merge ranges from multiple worksheets | Excel Worksheet Functions | |||
copy ranges from multiple worksheets | Excel Worksheet Functions | |||
Printing Multiple Ranges from Multiple Worksheets | Excel Programming |