Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to select all sheets
Hi All
I am needing help with code for a macro I want to have this macro run on every opening of file and compare to the current date. If the date is matched or past the current date I want to select all sheets in the work book and then clear all data in the sheets. And then save the file also. Can anyone help with this Thanks -- crunchin numbers |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to select all sheets
This workbook event macro assumes the reference date in is cell A1 in the
first sheet. If you open the workbook and the reference date is in the future, nothing happens. If you open the workbook and the reference date is in the past, then all the cell in the sheets are cleared and the workbook is saved: Private Sub Workbook_Open() Sheets(1).Activate If Date Range("A1").Value Then For Each sh In Sheets sh.Cells.Clear Next ActiveWorkbook.Save End If End Sub WARNING: mis-using this macro can be as bad as crossing the streams. -- Gary''s Student - gsnu200775 "belvy123" wrote: Hi All I am needing help with code for a macro I want to have this macro run on every opening of file and compare to the current date. If the date is matched or past the current date I want to select all sheets in the work book and then clear all data in the sheets. And then save the file also. Can anyone help with this Thanks -- crunchin numbers |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to select all sheets
Hi
The script works However I must not be putting it in the right spot If I put it in say module #1 then it does nothing. If I put it inside a sheet heading then it runs only when i execute it. What Am I doing wrong Thanks -- crunchin numbers "Gary''s Student" wrote: This workbook event macro assumes the reference date in is cell A1 in the first sheet. If you open the workbook and the reference date is in the future, nothing happens. If you open the workbook and the reference date is in the past, then all the cell in the sheets are cleared and the workbook is saved: Private Sub Workbook_Open() Sheets(1).Activate If Date Range("A1").Value Then For Each sh In Sheets sh.Cells.Clear Next ActiveWorkbook.Save End If End Sub WARNING: mis-using this macro can be as bad as crossing the streams. -- Gary''s Student - gsnu200775 "belvy123" wrote: Hi All I am needing help with code for a macro I want to have this macro run on every opening of file and compare to the current date. If the date is matched or past the current date I want to select all sheets in the work book and then clear all data in the sheets. And then save the file also. Can anyone help with this Thanks -- crunchin numbers |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to select all sheets
Because it is workbook code, it is very easy to install and use:
1. right-click the tiny Excel icon just to the left of File on the Menu bar 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (workbook code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200775 "belvy123" wrote: Hi The script works However I must not be putting it in the right spot If I put it in say module #1 then it does nothing. If I put it inside a sheet heading then it runs only when i execute it. What Am I doing wrong Thanks -- crunchin numbers "Gary''s Student" wrote: This workbook event macro assumes the reference date in is cell A1 in the first sheet. If you open the workbook and the reference date is in the future, nothing happens. If you open the workbook and the reference date is in the past, then all the cell in the sheets are cleared and the workbook is saved: Private Sub Workbook_Open() Sheets(1).Activate If Date Range("A1").Value Then For Each sh In Sheets sh.Cells.Clear Next ActiveWorkbook.Save End If End Sub WARNING: mis-using this macro can be as bad as crossing the streams. -- Gary''s Student - gsnu200775 "belvy123" wrote: Hi All I am needing help with code for a macro I want to have this macro run on every opening of file and compare to the current date. If the date is matched or past the current date I want to select all sheets in the work book and then clear all data in the sheets. And then save the file also. Can anyone help with this Thanks -- crunchin numbers |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to select all sheets
Works like a charm thanks
anyway to have it remove the code before save??? -- cruchnin numbers "Gary''s Student" wrote: Because it is workbook code, it is very easy to install and use: 1. right-click the tiny Excel icon just to the left of File on the Menu bar 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (workbook code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200775 "belvy123" wrote: Hi The script works However I must not be putting it in the right spot If I put it in say module #1 then it does nothing. If I put it inside a sheet heading then it runs only when i execute it. What Am I doing wrong Thanks -- crunchin numbers "Gary''s Student" wrote: This workbook event macro assumes the reference date in is cell A1 in the first sheet. If you open the workbook and the reference date is in the future, nothing happens. If you open the workbook and the reference date is in the past, then all the cell in the sheets are cleared and the workbook is saved: Private Sub Workbook_Open() Sheets(1).Activate If Date Range("A1").Value Then For Each sh In Sheets sh.Cells.Clear Next ActiveWorkbook.Save End If End Sub WARNING: mis-using this macro can be as bad as crossing the streams. -- Gary''s Student - gsnu200775 "belvy123" wrote: Hi All I am needing help with code for a macro I want to have this macro run on every opening of file and compare to the current date. If the date is matched or past the current date I want to select all sheets in the work book and then clear all data in the sheets. And then save the file also. Can anyone help with this Thanks -- crunchin numbers |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to select all sheets
I don't know how to do it.
-- Gary''s Student - gsnu200775 "belvy123" wrote: Works like a charm thanks anyway to have it remove the code before save??? -- cruchnin numbers "Gary''s Student" wrote: Because it is workbook code, it is very easy to install and use: 1. right-click the tiny Excel icon just to the left of File on the Menu bar 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (workbook code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200775 "belvy123" wrote: Hi The script works However I must not be putting it in the right spot If I put it in say module #1 then it does nothing. If I put it inside a sheet heading then it runs only when i execute it. What Am I doing wrong Thanks -- crunchin numbers "Gary''s Student" wrote: This workbook event macro assumes the reference date in is cell A1 in the first sheet. If you open the workbook and the reference date is in the future, nothing happens. If you open the workbook and the reference date is in the past, then all the cell in the sheets are cleared and the workbook is saved: Private Sub Workbook_Open() Sheets(1).Activate If Date Range("A1").Value Then For Each sh In Sheets sh.Cells.Clear Next ActiveWorkbook.Save End If End Sub WARNING: mis-using this macro can be as bad as crossing the streams. -- Gary''s Student - gsnu200775 "belvy123" wrote: Hi All I am needing help with code for a macro I want to have this macro run on every opening of file and compare to the current date. If the date is matched or past the current date I want to select all sheets in the work book and then clear all data in the sheets. And then save the file also. Can anyone help with this Thanks -- crunchin numbers |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to select all sheets
Hi Again
I am having one issue that i did not mention before I have protection on all sheets to keep people from deleting formulas. what do i need to add to this code to unprotect the sheets as the code trys to clear the cells Thanks -- cruncnin numbers "Gary''s Student" wrote: I don't know how to do it. -- Gary''s Student - gsnu200775 "belvy123" wrote: Works like a charm thanks anyway to have it remove the code before save??? -- cruchnin numbers "Gary''s Student" wrote: Because it is workbook code, it is very easy to install and use: 1. right-click the tiny Excel icon just to the left of File on the Menu bar 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (workbook code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200775 "belvy123" wrote: Hi The script works However I must not be putting it in the right spot If I put it in say module #1 then it does nothing. If I put it inside a sheet heading then it runs only when i execute it. What Am I doing wrong Thanks -- crunchin numbers "Gary''s Student" wrote: This workbook event macro assumes the reference date in is cell A1 in the first sheet. If you open the workbook and the reference date is in the future, nothing happens. If you open the workbook and the reference date is in the past, then all the cell in the sheets are cleared and the workbook is saved: Private Sub Workbook_Open() Sheets(1).Activate If Date Range("A1").Value Then For Each sh In Sheets sh.Cells.Clear Next ActiveWorkbook.Save End If End Sub WARNING: mis-using this macro can be as bad as crossing the streams. -- Gary''s Student - gsnu200775 "belvy123" wrote: Hi All I am needing help with code for a macro I want to have this macro run on every opening of file and compare to the current date. If the date is matched or past the current date I want to select all sheets in the work book and then clear all data in the sheets. And then save the file also. Can anyone help with this Thanks -- crunchin numbers |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to select all sheets
Macro starts
ActiveSheet.Unprotect Password:="justme" Macro does its stuff ActiveSheet Protect Password:="justme" End Sub On Wed, 26 Mar 2008 23:08:01 -0700, belvy123 wrote: Hi Again I am having one issue that i did not mention before I have protection on all sheets to keep people from deleting formulas. what do i need to add to this code to unprotect the sheets as the code trys to clear the cells Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to select all sheets | Excel Discussion (Misc queries) | |||
how can i select all sheets in excell? | Excel Worksheet Functions | |||
Macro to select and print sheets | Excel Discussion (Misc queries) | |||
Print Macro both sheets at once with option to select days etc | Excel Worksheet Functions | |||
USE KEYBOARD TO SELECT ALL SHEETS | Excel Worksheet Functions |