| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Contents Macro
I have a workbook with six worksheets. On Sheet 6, I want to create a macro
linked to an Excel button from the forms toolbar. I want the macro to only clear the contents of Sheet 1, Sheet 2, Sheet 3, Sheet 4, and Sheet 5 in the following cells C7, C9, C16:C23, and C26:C33. I am trying to write a code that will do this, any thoughts? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear Contents Macro
try this.
Sub clearcellsonsheets() For i = 1 To 5 ms = "sheet" & i Sheets(ms).Range("C7,C9,c16:C23,C26:C33").ClearCon tents Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "MSE" wrote in message ... I have a workbook with six worksheets. On Sheet 6, I want to create a macro linked to an Excel button from the forms toolbar. I want the macro to only clear the contents of Sheet 1, Sheet 2, Sheet 3, Sheet 4, and Sheet 5 in the following cells C7, C9, C16:C23, and C26:C33. I am trying to write a code that will do this, any thoughts? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear Contents Macro
Thank you for your input. When I try to run the Macro the screen switches
to visual basic and I get a message that says Run-time error '9' Subscript out of range, Continue, End, Debug, Help. Any thoughts? "Don Guillett" wrote: try this. Sub clearcellsonsheets() For i = 1 To 5 ms = "sheet" & i Sheets(ms).Range("C7,C9,c16:C23,C26:C33").ClearCon tents Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "MSE" wrote in message ... I have a workbook with six worksheets. On Sheet 6, I want to create a macro linked to an Excel button from the forms toolbar. I want the macro to only clear the contents of Sheet 1, Sheet 2, Sheet 3, Sheet 4, and Sheet 5 in the following cells C7, C9, C16:C23, and C26:C33. I am trying to write a code that will do this, any thoughts? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear Contents Macro
I have six sheets. Their names are Sheet 1, Sheet 2, Sheet 3, Sheet 4, Sheet
5, and Sheet 6. This part of the code is having the problem I think. Sheets(ms).Range("C7,C9,C16:C23,C26:C33").ClearCon tents It gets highlighted in yellow with a yellow arrow pointing to it when I try to run the macro. Any ideas? "Don Guillett" wrote: Number of sheets? Name of sheets? If desired, send your workbook with these snippets and the code to my address below -- Don Guillett Microsoft MVP Excel SalesAid Software "MSE" wrote in message ... Thank you for your input. When I try to run the Macro the screen switches to visual basic and I get a message that says Run-time error '9' Subscript out of range, Continue, End, Debug, Help. Any thoughts? "Don Guillett" wrote: try this. Sub clearcellsonsheets() For i = 1 To 5 ms = "sheet" & i Sheets(ms).Range("C7,C9,c16:C23,C26:C33").ClearCon tents Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "MSE" wrote in message ... I have a workbook with six worksheets. On Sheet 6, I want to create a macro linked to an Excel button from the forms toolbar. I want the macro to only clear the contents of Sheet 1, Sheet 2, Sheet 3, Sheet 4, and Sheet 5 in the following cells C7, C9, C16:C23, and C26:C33. I am trying to write a code that will do this, any thoughts? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear Contents Macro
Send your workbook to my address below and I'll have a look
-- Don Guillett Microsoft MVP Excel SalesAid Software "MSE" wrote in message ... I have six sheets. Their names are Sheet 1, Sheet 2, Sheet 3, Sheet 4, Sheet 5, and Sheet 6. This part of the code is having the problem I think. Sheets(ms).Range("C7,C9,C16:C23,C26:C33").ClearCon tents It gets highlighted in yellow with a yellow arrow pointing to it when I try to run the macro. Any ideas? "Don Guillett" wrote: Number of sheets? Name of sheets? If desired, send your workbook with these snippets and the code to my address below -- Don Guillett Microsoft MVP Excel SalesAid Software "MSE" wrote in message ... Thank you for your input. When I try to run the Macro the screen switches to visual basic and I get a message that says Run-time error '9' Subscript out of range, Continue, End, Debug, Help. Any thoughts? "Don Guillett" wrote: try this. Sub clearcellsonsheets() For i = 1 To 5 ms = "sheet" & i Sheets(ms).Range("C7,C9,c16:C23,C26:C33").ClearCon tents Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "MSE" wrote in message ... I have a workbook with six worksheets. On Sheet 6, I want to create a macro linked to an Excel button from the forms toolbar. I want the macro to only clear the contents of Sheet 1, Sheet 2, Sheet 3, Sheet 4, and Sheet 5 in the following cells C7, C9, C16:C23, and C26:C33. I am trying to write a code that will do this, any thoughts? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear Contents Macro
Try changing "sheet" to "Sheet"
Gord Dibben MS Excel MVP On Sat, 21 Jun 2008 10:12:00 -0700, MSE wrote: Thank you for your input. When I try to run the Macro the screen switches to visual basic and I get a message that says Run-time error '9' Subscript out of range, Continue, End, Debug, Help. Any thoughts? "Don Guillett" wrote: try this. Sub clearcellsonsheets() For i = 1 To 5 ms = "sheet" & i Sheets(ms).Range("C7,C9,c16:C23,C26:C33").ClearCon tents Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "MSE" wrote in message ... I have a workbook with six worksheets. On Sheet 6, I want to create a macro linked to an Excel button from the forms toolbar. I want the macro to only clear the contents of Sheet 1, Sheet 2, Sheet 3, Sheet 4, and Sheet 5 in the following cells C7, C9, C16:C23, and C26:C33. I am trying to write a code that will do this, any thoughts? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear Contents Macro
If your sheet names really have that space character, then change:
ms = "sheet" & i to ms = "sheet " & i MSE wrote: I have six sheets. Their names are Sheet 1, Sheet 2, Sheet 3, Sheet 4, Sheet 5, and Sheet 6. This part of the code is having the problem I think. Sheets(ms).Range("C7,C9,C16:C23,C26:C33").ClearCon tents It gets highlighted in yellow with a yellow arrow pointing to it when I try to run the macro. Any ideas? "Don Guillett" wrote: Number of sheets? Name of sheets? If desired, send your workbook with these snippets and the code to my address below -- Don Guillett Microsoft MVP Excel SalesAid Software "MSE" wrote in message ... Thank you for your input. When I try to run the Macro the screen switches to visual basic and I get a message that says Run-time error '9' Subscript out of range, Continue, End, Debug, Help. Any thoughts? "Don Guillett" wrote: try this. Sub clearcellsonsheets() For i = 1 To 5 ms = "sheet" & i Sheets(ms).Range("C7,C9,c16:C23,C26:C33").ClearCon tents Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "MSE" wrote in message ... I have a workbook with six worksheets. On Sheet 6, I want to create a macro linked to an Excel button from the forms toolbar. I want the macro to only clear the contents of Sheet 1, Sheet 2, Sheet 3, Sheet 4, and Sheet 5 in the following cells C7, C9, C16:C23, and C26:C33. I am trying to write a code that will do this, any thoughts? -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear Contents Macro
Sub clearcellsonsheets()
For i = 1 To Worksheets.Count If Sheets(i).Name < "Department Total" Then Sheets(i).Range("C7,C9,c16:C23,C26:C33").ClearCont ents End If Next i End Sub Didn't work so I changed to ABOVE & suggested other changes to wb 'Sub clearcellsonsheetsold() 'For i = 1 To 5 'ms = "Sheet " & i 'Sheets(ms).Range("C7,C9,c16:C23,C26:C33").ClearCo ntents 'Next i 'End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Send your workbook to my address below and I'll have a look -- Don Guillett Microsoft MVP Excel SalesAid Software "MSE" wrote in message ... I have six sheets. Their names are Sheet 1, Sheet 2, Sheet 3, Sheet 4, Sheet 5, and Sheet 6. This part of the code is having the problem I think. Sheets(ms).Range("C7,C9,C16:C23,C26:C33").ClearCon tents It gets highlighted in yellow with a yellow arrow pointing to it when I try to run the macro. Any ideas? "Don Guillett" wrote: Number of sheets? Name of sheets? If desired, send your workbook with these snippets and the code to my address below -- Don Guillett Microsoft MVP Excel SalesAid Software "MSE" wrote in message ... Thank you for your input. When I try to run the Macro the screen switches to visual basic and I get a message that says Run-time error '9' Subscript out of range, Continue, End, Debug, Help. Any thoughts? "Don Guillett" wrote: try this. Sub clearcellsonsheets() For i = 1 To 5 ms = "sheet" & i Sheets(ms).Range("C7,C9,c16:C23,C26:C33").ClearCon tents Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "MSE" wrote in message ... I have a workbook with six worksheets. On Sheet 6, I want to create a macro linked to an Excel button from the forms toolbar. I want the macro to only clear the contents of Sheet 1, Sheet 2, Sheet 3, Sheet 4, and Sheet 5 in the following cells C7, C9, C16:C23, and C26:C33. I am trying to write a code that will do this, any thoughts? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to clear contents and put an X | Excel Discussion (Misc queries) | |||
| Macro to clear range contents when cell contents are changed by us | Excel Programming | |||
| Clear Contents Macro | Excel Programming | |||
| Clear contents macro | Excel Programming | |||
| Need macro to clear contents | Excel Programming | |||
Linear Mode
