Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
I have an Excel workbook with 15 worksheets in it that each week I need to
zero out the data from four (4) columns that are next to each other to get ready for the next week. I would like a macro to do either zero out one and then jump to the next or do them all at one time. Thanks Beep Beep |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
This will clear out Columns A thru D
Sub clearWorksheetLoop() Dim WS_Count As Integer Dim i As Integer WS_Count = _ ActiveWorkbook.Worksheets.Count ' Begin the loop. For i = 1 To WS_Count ActiveWorkbook.Worksheets(i) _ .Columns("A:D").ClearContents Next i End Sub "Beep Beep" wrote: I have an Excel workbook with 15 worksheets in it that each week I need to zero out the data from four (4) columns that are next to each other to get ready for the next week. I would like a macro to do either zero out one and then jump to the next or do them all at one time. Thanks Beep Beep |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
As a macro you need to do it one sheet at a time. Did you need all 15 sheets
or are some excluded? did you need the whole column or just parts of the column. This will go through all sheets and clear out a range of cells sub ClearStuff() dim wks as worksheet for each wks in worksheets with wks .range(.range("B2"), .cells(rows.count, "E")).clearcontents end with next wks end sub -- HTH... Jim Thomlinson "Beep Beep" wrote: I have an Excel workbook with 15 worksheets in it that each week I need to zero out the data from four (4) columns that are next to each other to get ready for the next week. I would like a macro to do either zero out one and then jump to the next or do them all at one time. Thanks Beep Beep |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
Jim yes I have more sheets then the 15 I mentioned. There are only 15 that I
need to change on a weekly basis. I guess I would have to have the name of the tab's in the macro or as you suggested one at a time "Jim Thomlinson" wrote: As a macro you need to do it one sheet at a time. Did you need all 15 sheets or are some excluded? did you need the whole column or just parts of the column. This will go through all sheets and clear out a range of cells sub ClearStuff() dim wks as worksheet for each wks in worksheets with wks .range(.range("B2"), .cells(rows.count, "E")).clearcontents end with next wks end sub -- HTH... Jim Thomlinson "Beep Beep" wrote: I have an Excel workbook with 15 worksheets in it that each week I need to zero out the data from four (4) columns that are next to each other to get ready for the next week. I would like a macro to do either zero out one and then jump to the next or do them all at one time. Thanks Beep Beep |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
In that case try this
sub ClearStuff() dim wks as worksheet for each wks in worksheets with wks select case .name Case "Sheets", "I", "Don't", "Want", "Cleared" 'sheet names Case Else .range(.range("B2"), .cells(rows.count, "E")).clearcontents End Select end with next wks end sub -- HTH... Jim Thomlinson "Beep Beep" wrote: Jim yes I have more sheets then the 15 I mentioned. There are only 15 that I need to change on a weekly basis. I guess I would have to have the name of the tab's in the macro or as you suggested one at a time "Jim Thomlinson" wrote: As a macro you need to do it one sheet at a time. Did you need all 15 sheets or are some excluded? did you need the whole column or just parts of the column. This will go through all sheets and clear out a range of cells sub ClearStuff() dim wks as worksheet for each wks in worksheets with wks .range(.range("B2"), .cells(rows.count, "E")).clearcontents end with next wks end sub -- HTH... Jim Thomlinson "Beep Beep" wrote: I have an Excel workbook with 15 worksheets in it that each week I need to zero out the data from four (4) columns that are next to each other to get ready for the next week. I would like a macro to do either zero out one and then jump to the next or do them all at one time. Thanks Beep Beep |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoRun Macro with a delay to give user the choice to cancel the macro | Excel Programming | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |