Hi everybody,

is there a way to run a command at a certain date?
E.g.: Delete all formulas of worksheet "A" and replace them by the
cells value on the 01.04.2005 10:00am.

Any help is very much appreciated.

Thanks
Norbert

You haven't explained fully...are you saying that you know where the data for
a given date is stored?
It sounds like you've actually LOST the data for a given date & are looking
to recover it. Fraid not. IF you're working for a company and IF you save
down your work to a network share, and IF your IT do backups every night then
they might be able to restore a workbook from a given date.

"Orion" wrote:

Hi everybody,

is there a way to run a command at a certain date?
E.g.: Delete all formulas of worksheet "A" and replace them by the
cells value on the 01.04.2005 10:00am.

Any help is very much appreciated.

Thanks
Norbert

Norbert,
You could put a check in the Workbook_Open event and check:
<Air-code
If Now()MyDate then
With Worksheets(MyWS)
.Cells.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
False, Transpose:=False
End with
End if
</Air-code

NickHK

"Orion" wrote in message
...
Hi everybody,

is there a way to run a command at a certain date?
E.g.: Delete all formulas of worksheet "A" and replace them by the
cells value on the 01.04.2005 10:00am.

Any help is very much appreciated.

Thanks
Norbert

Norbert,
The previous post assumes it is OK to run this the next time the WB is
opened, whenever that is.
If you need to run it exactly at that time, may the Task Scheduler would be
better.

NickHK

"NickHK" wrote in message
...
Norbert,
You could put a check in the Workbook_Open event and check:
<Air-code
If Now()MyDate then
With Worksheets(MyWS)
.Cells.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
False, Transpose:=False
End with
End if
</Air-code

NickHK

"Orion" wrote in message
...
Hi everybody,

is there a way to run a command at a certain date?
E.g.: Delete all formulas of worksheet "A" and replace them by the
cells value on the 01.04.2005 10:00am.

Any help is very much appreciated.

Thanks
Norbert

Hello,
sorry that I didn't expressed myself in a better way.
What I'm trying to do is to replace all formulas used on a certain
worksheet (eg. worksheet "A") with their current values on a certain
day in the future.

The macro should do something like this:

by opening the worksheet, check the date
if it's eg. 01.04.2005, then go to worksheet "A",
select all cells,
copy,
past values
save
if it's not the 01.04.2005 yet, then do nothing.


the date will be determined by myself and typed into the macro.


The whole procedure is just to limit the period in which the
spreadsheet will work.

It's like an evaluation software which won't work anymore after a
certain period of days.

Thanks,
Norbert


On Mon, 31 Jan 2005 23:41:03 -0800, "Patrick Molloy"
wrote:

You haven't explained fully...are you saying that you know where the data for
a given date is stored?
It sounds like you've actually LOST the data for a given date & are looking
to recover it. Fraid not. IF you're working for a company and IF you save
down your work to a network share, and IF your IT do backups every night then
they might be able to restore a workbook from a given date.

"Orion" wrote:

Hi everybody,

is there a way to run a command at a certain date?
E.g.: Delete all formulas of worksheet "A" and replace them by the
cells value on the 01.04.2005 10:00am.

Any help is very much appreciated.

Thanks
Norbert

Nick,
thank you very much for your suggestions. Your Air-code (whatever that
is (it reminds me on sth. like playing air-guitar?!?) will work
exactely as I want, I just hope I get it going.

The scheduler wouldn't be better in this case as the spreadsheet won't
run on my computer and I don't have any influence on the scheduler of
the other side.

But thanks anyway.

Regards,
Norbert



On Tue, 1 Feb 2005 17:06:14 +0800, "NickHK"
wrote:

Norbert,
The previous post assumes it is OK to run this the next time the WB is
opened, whenever that is.
If you need to run it exactly at that time, may the Task Scheduler would be
better.

NickHK

"NickHK" wrote in message
...
Norbert,
You could put a check in the Workbook_Open event and check:
<Air-code
If Now()MyDate then
With Worksheets(MyWS)
.Cells.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
False, Transpose:=False
End with
End if
</Air-code

NickHK

"Orion" wrote in message
...
Hi everybody,

is there a way to run a command at a certain date?
E.g.: Delete all formulas of worksheet "A" and replace them by the
cells value on the 01.04.2005 10:00am.

Any help is very much appreciated.

Thanks
Norbert

I tried this following code, which doesn't do the job. Can anyone tell
me please, why?

Private Sub App_WorkbookOpen()
'
If Now() 31 / 1 / 2005 Then
With Worksheets(Sheet1)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub


Thanks,
Norbert


On Tue, 01 Feb 2005 12:37:08 +0200, Orion wrote:

Nick,
thank you very much for your suggestions. Your Air-code (whatever that
is (it reminds me on sth. like playing air-guitar?!?) will work
exactely as I want, I just hope I get it going.

The scheduler wouldn't be better in this case as the spreadsheet won't
run on my computer and I don't have any influence on the scheduler of
the other side.

But thanks anyway.

Regards,
Norbert



On Tue, 1 Feb 2005 17:06:14 +0800, "NickHK"
wrote:

Norbert,
The previous post assumes it is OK to run this the next time the WB is
opened, whenever that is.
If you need to run it exactly at that time, may the Task Scheduler would be
better.

NickHK

"NickHK" wrote in message
...
Norbert,
You could put a check in the Workbook_Open event and check:
<Air-code
If Now()MyDate then
With Worksheets(MyWS)
.Cells.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
False, Transpose:=False
End with
End if
</Air-code

NickHK

"Orion" wrote in message
...
Hi everybody,

is there a way to run a command at a certain date?
E.g.: Delete all formulas of worksheet "A" and replace them by the
cells value on the 01.04.2005 10:00am.

Any help is very much appreciated.

Thanks
Norbert

Could please someone check my code. Something seems to be missing.
When I open the worksheet nothing happens.


Private Sub App_WorkbookOpen()
'
If Now() 31 / 1 / 2005 Then
With Worksheets(Sheet1)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub


Regards,
Norbert

Norbert,
First, you should be using the event that Excel gives you, "Private Sub
Workbook_Open()", located on ThisWorkbook. At present your code is not
firing.
When it does, you will find an error on the first line. Check the help for
the Date Data Type.

Let us know if you get it to work.

NickHK

"Norbert Jaeger" wrote in message
...
Could please someone check my code. Something seems to be missing.
When I open the worksheet nothing happens.


Private Sub App_WorkbookOpen()
'
If Now() 31 / 1 / 2005 Then
With Worksheets(Sheet1)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub


Regards,
Norbert

Hi Nick,

thank you very much it worked, just by using
"Private Sub Workbook_Open()" event
The date data type seems to be alright.

Thanks for your help.
Where do I know from which event to take?
I found this one
"Private Sub App_WorkbookOpen()"
somewhere in the help.

Regards,
Norbert


On Wed, 2 Feb 2005 09:39:45 +0800, "NickHK"
wrote:

Norbert,
First, you should be using the event that Excel gives you, "Private Sub
Workbook_Open()", located on ThisWorkbook. At present your code is not
firing.
When it does, you will find an error on the first line. Check the help for
the Date Data Type.

Let us know if you get it to work.

NickHK

"Norbert Jaeger" wrote in message
.. .
Could please someone check my code. Something seems to be missing.
When I open the worksheet nothing happens.


Private Sub App_WorkbookOpen()
'
If Now() 31 / 1 / 2005 Then
With Worksheets(Sheet1)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub


Regards,
Norbert

Norbert,
In the VBA IDE, double-click the Excel object to work with, e.g.
ThisWorkbook.
At the top, left of the code pane, you'll see a combo box that has either
"(General)" or "Workbook". Select "Workbook". At the same level, on the
right, you now have a list of possible events for the item selected on the
left.

"Private Sub App_WorkbookOpen()", I assume, would be for Application level
events, which are slightly different

NickHK

"Orion" wrote in message
...
Hi Nick,

thank you very much it worked, just by using
"Private Sub Workbook_Open()" event
The date data type seems to be alright.

Thanks for your help.
Where do I know from which event to take?
I found this one
"Private Sub App_WorkbookOpen()"
somewhere in the help.

Regards,
Norbert


On Wed, 2 Feb 2005 09:39:45 +0800, "NickHK"
wrote:

Norbert,
First, you should be using the event that Excel gives you, "Private Sub
Workbook_Open()", located on ThisWorkbook. At present your code is not
firing.
When it does, you will find an error on the first line. Check the help
for
the Date Data Type.

Let us know if you get it to work.

NickHK

"Norbert Jaeger" wrote in message
.. .
Could please someone check my code. Something seems to be missing.
When I open the worksheet nothing happens.


Private Sub App_WorkbookOpen()
'
If Now() 31 / 1 / 2005 Then
With Worksheets(Sheet1)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub


Regards,
Norbert

Nick,

thanks for your explanation. I never tried to choose something from
that list, otherwise I would have seen that one actually gets the
event from Excel automatically.

just one other thing: Assuming my Workbook has 3 worksheet and when
opening the workbook e.g. "Sheet3" is active.
How would I get my macro to go to "Sheet1", do its job and come back
to the previously active sheet, whichever it is?

I would like the user not to see, that a macro is running when he
opens the workbook. Therefor I put at the beginning of the macro
< Application.ScreenUpdating = False

At the moment the workbook opens with Sheet3 active. The macro does
its job, but on Sheet3 instead on Sheet1.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() 31 / 1 / 2005 Then
With Worksheets("Sheet1") ' this does not help!?!
Cells.Select ' it selects the cells
on Sheet3 instead Sheet1
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub





On Wed, 2 Feb 2005 15:38:24 +0800, "NickHK"
wrote:

Norbert,
In the VBA IDE, double-click the Excel object to work with, e.g.
ThisWorkbook.
At the top, left of the code pane, you'll see a combo box that has either
"(General)" or "Workbook". Select "Workbook". At the same level, on the
right, you now have a list of possible events for the item selected on the
left.

"Private Sub App_WorkbookOpen()", I assume, would be for Application level
events, which are slightly different

NickHK

"Orion" wrote in message
.. .
Hi Nick,

thank you very much it worked, just by using
"Private Sub Workbook_Open()" event
The date data type seems to be alright.

Thanks for your help.
Where do I know from which event to take?
I found this one
"Private Sub App_WorkbookOpen()"
somewhere in the help.

Regards,
Norbert


On Wed, 2 Feb 2005 09:39:45 +0800, "NickHK"
wrote:

Norbert,
First, you should be using the event that Excel gives you, "Private Sub
Workbook_Open()", located on ThisWorkbook. At present your code is not
firing.
When it does, you will find an error on the first line. Check the help
for
the Date Data Type.

Let us know if you get it to work.

NickHK

"Norbert Jaeger" wrote in message
.. .
Could please someone check my code. Something seems to be missing.
When I open the worksheet nothing happens.


Private Sub App_WorkbookOpen()
'
If Now() 31 / 1 / 2005 Then
With Worksheets(Sheet1)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub


Regards,
Norbert

Norbert,
It's because you are SELECTing the cells to work on. This is rarely
necessary in VBA as you can work on the object anyway.
Try something like:
With Worksheets("Sheet3")
.Cells.Copy
.PasteSpecial........

NickHK

"Orion" wrote in message
...
Nick,

thanks for your explanation. I never tried to choose something from
that list, otherwise I would have seen that one actually gets the
event from Excel automatically.

just one other thing: Assuming my Workbook has 3 worksheet and when
opening the workbook e.g. "Sheet3" is active.
How would I get my macro to go to "Sheet1", do its job and come back
to the previously active sheet, whichever it is?

I would like the user not to see, that a macro is running when he
opens the workbook. Therefor I put at the beginning of the macro
< Application.ScreenUpdating = False

At the moment the workbook opens with Sheet3 active. The macro does
its job, but on Sheet3 instead on Sheet1.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() 31 / 1 / 2005 Then
With Worksheets("Sheet1") ' this does not help!?!
Cells.Select ' it selects the cells
on Sheet3 instead Sheet1
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub





On Wed, 2 Feb 2005 15:38:24 +0800, "NickHK"
wrote:

Norbert,
In the VBA IDE, double-click the Excel object to work with, e.g.
ThisWorkbook.
At the top, left of the code pane, you'll see a combo box that has either
"(General)" or "Workbook". Select "Workbook". At the same level, on the
right, you now have a list of possible events for the item selected on
the
left.

"Private Sub App_WorkbookOpen()", I assume, would be for Application
level
events, which are slightly different

NickHK

"Orion" wrote in message
.. .
Hi Nick,

thank you very much it worked, just by using
"Private Sub Workbook_Open()" event
The date data type seems to be alright.

Thanks for your help.
Where do I know from which event to take?
I found this one
"Private Sub App_WorkbookOpen()"
somewhere in the help.

Regards,
Norbert


On Wed, 2 Feb 2005 09:39:45 +0800, "NickHK"
wrote:

Norbert,
First, you should be using the event that Excel gives you, "Private
Sub
Workbook_Open()", located on ThisWorkbook. At present your code is not
firing.
When it does, you will find an error on the first line. Check the help
for
the Date Data Type.

Let us know if you get it to work.

NickHK

"Norbert Jaeger" wrote in message
.. .
Could please someone check my code. Something seems to be missing.
When I open the worksheet nothing happens.


Private Sub App_WorkbookOpen()
'
If Now() 31 / 1 / 2005 Then
With Worksheets(Sheet1)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub


Regards,
Norbert

Hi Nick,

thanks for your patience, man!
Now I'm getting an error message:
run time error 1004
Application-defined or object-defined error
I read through the help, but that is all greek to me.

Do you discover anything wrong in the code?
It doesn't highlight anything, so I don't know what's wrong but
the cells on Sheet1 are copied although the active sheet is Sheet3.
That I can see at the flickering border when I switch to Sheet1.
So, up to there it works. I think the .PasteSpecial command is the
problem.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() 31 / 1 / 2005 Then
With Worksheets("Sheet1")
.Cells.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

Regards,
Norbert


On Wed, 2 Feb 2005 16:25:25 +0800, "NickHK"
wrote:

Norbert,
It's because you are SELECTing the cells to work on. This is rarely
necessary in VBA as you can work on the object anyway.
Try something like:
With Worksheets("Sheet3")
.Cells.Copy
.PasteSpecial........

NickHK

"Orion" wrote in message
.. .
Nick,

thanks for your explanation. I never tried to choose something from
that list, otherwise I would have seen that one actually gets the
event from Excel automatically.

just one other thing: Assuming my Workbook has 3 worksheet and when
opening the workbook e.g. "Sheet3" is active.
How would I get my macro to go to "Sheet1", do its job and come back
to the previously active sheet, whichever it is?

I would like the user not to see, that a macro is running when he
opens the workbook. Therefor I put at the beginning of the macro
< Application.ScreenUpdating = False

At the moment the workbook opens with Sheet3 active. The macro does
its job, but on Sheet3 instead on Sheet1.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() 31 / 1 / 2005 Then
With Worksheets("Sheet1") ' this does not help!?!
Cells.Select ' it selects the cells
on Sheet3 instead Sheet1
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub





On Wed, 2 Feb 2005 15:38:24 +0800, "NickHK"
wrote:

Norbert,
In the VBA IDE, double-click the Excel object to work with, e.g.
ThisWorkbook.
At the top, left of the code pane, you'll see a combo box that has either
"(General)" or "Workbook". Select "Workbook". At the same level, on the
right, you now have a list of possible events for the item selected on
the
left.

"Private Sub App_WorkbookOpen()", I assume, would be for Application
level
events, which are slightly different

NickHK

"Orion" wrote in message
.. .
Hi Nick,

thank you very much it worked, just by using
"Private Sub Workbook_Open()" event
The date data type seems to be alright.

Thanks for your help.
Where do I know from which event to take?
I found this one
"Private Sub App_WorkbookOpen()"
somewhere in the help.

Regards,
Norbert


On Wed, 2 Feb 2005 09:39:45 +0800, "NickHK"
wrote:

Norbert,
First, you should be using the event that Excel gives you, "Private
Sub
Workbook_Open()", located on ThisWorkbook. At present your code is not
firing.
When it does, you will find an error on the first line. Check the help
for
the Date Data Type.

Let us know if you get it to work.

NickHK

"Norbert Jaeger" wrote in message
.. .
Could please someone check my code. Something seems to be missing.
When I open the worksheet nothing happens.


Private Sub App_WorkbookOpen()
'
If Now() 31 / 1 / 2005 Then
With Worksheets(Sheet1)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub


Regards,
Norbert

Norbert,
..PasteSpecial should be a method of the range object. So try this:
With Worksheets("Sheet1").Cells
.Copy
.PasteSpecial

Also if you change your date to say the year 2050, is the code still
executed ?
If so I suggest you look at the date data type in help.

NickHK

"Orion" wrote in message
...
Hi Nick,

thanks for your patience, man!
Now I'm getting an error message:
run time error 1004
Application-defined or object-defined error
I read through the help, but that is all greek to me.

Do you discover anything wrong in the code?
It doesn't highlight anything, so I don't know what's wrong but
the cells on Sheet1 are copied although the active sheet is Sheet3.
That I can see at the flickering border when I switch to Sheet1.
So, up to there it works. I think the .PasteSpecial command is the
problem.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() 31 / 1 / 2005 Then
With Worksheets("Sheet1")
.Cells.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

Regards,
Norbert


On Wed, 2 Feb 2005 16:25:25 +0800, "NickHK"
wrote:

Norbert,
It's because you are SELECTing the cells to work on. This is rarely
necessary in VBA as you can work on the object anyway.
Try something like:
With Worksheets("Sheet3")
.Cells.Copy
.PasteSpecial........

NickHK

"Orion" wrote in message
.. .
Nick,

thanks for your explanation. I never tried to choose something from
that list, otherwise I would have seen that one actually gets the
event from Excel automatically.

just one other thing: Assuming my Workbook has 3 worksheet and when
opening the workbook e.g. "Sheet3" is active.
How would I get my macro to go to "Sheet1", do its job and come back
to the previously active sheet, whichever it is?

I would like the user not to see, that a macro is running when he
opens the workbook. Therefor I put at the beginning of the macro
< Application.ScreenUpdating = False

At the moment the workbook opens with Sheet3 active. The macro does
its job, but on Sheet3 instead on Sheet1.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() 31 / 1 / 2005 Then
With Worksheets("Sheet1") ' this does not help!?!
Cells.Select ' it selects the cells
on Sheet3 instead Sheet1
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub





On Wed, 2 Feb 2005 15:38:24 +0800, "NickHK"
wrote:

Norbert,
In the VBA IDE, double-click the Excel object to work with, e.g.
ThisWorkbook.
At the top, left of the code pane, you'll see a combo box that has
either
"(General)" or "Workbook". Select "Workbook". At the same level, on
the
right, you now have a list of possible events for the item selected on
the
left.

"Private Sub App_WorkbookOpen()", I assume, would be for Application
level
events, which are slightly different

NickHK

"Orion" wrote in message
.. .
Hi Nick,

thank you very much it worked, just by using
"Private Sub Workbook_Open()" event
The date data type seems to be alright.

Thanks for your help.
Where do I know from which event to take?
I found this one
"Private Sub App_WorkbookOpen()"
somewhere in the help.

Regards,
Norbert


On Wed, 2 Feb 2005 09:39:45 +0800, "NickHK"
wrote:

Norbert,
First, you should be using the event that Excel gives you, "Private
Sub
Workbook_Open()", located on ThisWorkbook. At present your code is
not
firing.
When it does, you will find an error on the first line. Check the
help
for
the Date Data Type.

Let us know if you get it to work.

NickHK

"Norbert Jaeger" wrote in message
.. .
Could please someone check my code. Something seems to be
missing.
When I open the worksheet nothing happens.


Private Sub App_WorkbookOpen()
'
If Now() 31 / 1 / 2005 Then
With Worksheets(Sheet1)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub


Regards,
Norbert

Nick,

this is the code which finally works to my satisfaction!
Thanks to you!!!

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() #1/31/2050# Then
With Worksheets("Sheet1").Cells
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

Best regards,
Norbert




On Thu, 3 Feb 2005 09:47:55 +0800, "NickHK"
wrote:

Norbert,
.PasteSpecial should be a method of the range object. So try this:
With Worksheets("Sheet1").Cells
.Copy
.PasteSpecial

Also if you change your date to say the year 2050, is the code still
executed ?
If so I suggest you look at the date data type in help.

NickHK

"Orion" wrote in message
.. .
Hi Nick,

thanks for your patience, man!
Now I'm getting an error message:
run time error 1004
Application-defined or object-defined error
I read through the help, but that is all greek to me.

Do you discover anything wrong in the code?
It doesn't highlight anything, so I don't know what's wrong but
the cells on Sheet1 are copied although the active sheet is Sheet3.
That I can see at the flickering border when I switch to Sheet1.
So, up to there it works. I think the .PasteSpecial command is the
problem.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() 31 / 1 / 2005 Then
With Worksheets("Sheet1")
.Cells.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

Regards,
Norbert


On Wed, 2 Feb 2005 16:25:25 +0800, "NickHK"
wrote:

Norbert,
It's because you are SELECTing the cells to work on. This is rarely
necessary in VBA as you can work on the object anyway.
Try something like:
With Worksheets("Sheet3")
.Cells.Copy
.PasteSpecial........

NickHK

"Orion" wrote in message
.. .
Nick,

thanks for your explanation. I never tried to choose something from
that list, otherwise I would have seen that one actually gets the
event from Excel automatically.

just one other thing: Assuming my Workbook has 3 worksheet and when
opening the workbook e.g. "Sheet3" is active.
How would I get my macro to go to "Sheet1", do its job and come back
to the previously active sheet, whichever it is?

I would like the user not to see, that a macro is running when he
opens the workbook. Therefor I put at the beginning of the macro
< Application.ScreenUpdating = False

At the moment the workbook opens with Sheet3 active. The macro does
its job, but on Sheet3 instead on Sheet1.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() 31 / 1 / 2005 Then
With Worksheets("Sheet1") ' this does not help!?!
Cells.Select ' it selects the cells
on Sheet3 instead Sheet1
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub





On Wed, 2 Feb 2005 15:38:24 +0800, "NickHK"
wrote:

Norbert,
In the VBA IDE, double-click the Excel object to work with, e.g.
ThisWorkbook.
At the top, left of the code pane, you'll see a combo box that has
either
"(General)" or "Workbook". Select "Workbook". At the same level, on
the
right, you now have a list of possible events for the item selected on
the
left.

"Private Sub App_WorkbookOpen()", I assume, would be for Application
level
events, which are slightly different

NickHK

"Orion" wrote in message
.. .
Hi Nick,

thank you very much it worked, just by using
"Private Sub Workbook_Open()" event
The date data type seems to be alright.

Thanks for your help.
Where do I know from which event to take?
I found this one
"Private Sub App_WorkbookOpen()"
somewhere in the help.

Regards,
Norbert


On Wed, 2 Feb 2005 09:39:45 +0800, "NickHK"
wrote:

Norbert,
First, you should be using the event that Excel gives you, "Private
Sub
Workbook_Open()", located on ThisWorkbook. At present your code is
not
firing.
When it does, you will find an error on the first line. Check the
help
for
the Date Data Type.

Let us know if you get it to work.

NickHK

"Norbert Jaeger" wrote in message
.. .
Could please someone check my code. Something seems to be
missing.
When I open the worksheet nothing happens.


Private Sub App_WorkbookOpen()
'
If Now() 31 / 1 / 2005 Then
With Worksheets(Sheet1)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub


Regards,
Norbert

Norbert,
As written though, you will have to wait some 45 years to see its effect.

NickHK

"Orion" wrote in message
...
Nick,

this is the code which finally works to my satisfaction!
Thanks to you!!!

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() #1/31/2050# Then
With Worksheets("Sheet1").Cells
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

Best regards,
Norbert




On Thu, 3 Feb 2005 09:47:55 +0800, "NickHK"
wrote:

Norbert,
.PasteSpecial should be a method of the range object. So try this:
With Worksheets("Sheet1").Cells
.Copy
.PasteSpecial

Also if you change your date to say the year 2050, is the code still
executed ?
If so I suggest you look at the date data type in help.

NickHK

"Orion" wrote in message
.. .
Hi Nick,

thanks for your patience, man!
Now I'm getting an error message:
run time error 1004
Application-defined or object-defined error
I read through the help, but that is all greek to me.

Do you discover anything wrong in the code?
It doesn't highlight anything, so I don't know what's wrong but
the cells on Sheet1 are copied although the active sheet is Sheet3.
That I can see at the flickering border when I switch to Sheet1.
So, up to there it works. I think the .PasteSpecial command is the
problem.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() 31 / 1 / 2005 Then
With Worksheets("Sheet1")
.Cells.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

Regards,
Norbert


On Wed, 2 Feb 2005 16:25:25 +0800, "NickHK"
wrote:

Norbert,
It's because you are SELECTing the cells to work on. This is rarely
necessary in VBA as you can work on the object anyway.
Try something like:
With Worksheets("Sheet3")
.Cells.Copy
.PasteSpecial........

NickHK

"Orion" wrote in message
.. .
Nick,

thanks for your explanation. I never tried to choose something from
that list, otherwise I would have seen that one actually gets the
event from Excel automatically.

just one other thing: Assuming my Workbook has 3 worksheet and when
opening the workbook e.g. "Sheet3" is active.
How would I get my macro to go to "Sheet1", do its job and come back
to the previously active sheet, whichever it is?

I would like the user not to see, that a macro is running when he
opens the workbook. Therefor I put at the beginning of the macro
< Application.ScreenUpdating = False

At the moment the workbook opens with Sheet3 active. The macro does
its job, but on Sheet3 instead on Sheet1.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() 31 / 1 / 2005 Then
With Worksheets("Sheet1") ' this does not help!?!
Cells.Select ' it selects the cells
on Sheet3 instead Sheet1
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub





On Wed, 2 Feb 2005 15:38:24 +0800, "NickHK"
wrote:

Norbert,
In the VBA IDE, double-click the Excel object to work with, e.g.
ThisWorkbook.
At the top, left of the code pane, you'll see a combo box that has
either
"(General)" or "Workbook". Select "Workbook". At the same level, on
the
right, you now have a list of possible events for the item selected
on
the
left.

"Private Sub App_WorkbookOpen()", I assume, would be for
Application
level
events, which are slightly different

NickHK

"Orion" wrote in message
.. .
Hi Nick,

thank you very much it worked, just by using
"Private Sub Workbook_Open()" event
The date data type seems to be alright.

Thanks for your help.
Where do I know from which event to take?
I found this one
"Private Sub App_WorkbookOpen()"
somewhere in the help.

Regards,
Norbert


On Wed, 2 Feb 2005 09:39:45 +0800, "NickHK"

wrote:

Norbert,
First, you should be using the event that Excel gives you,
"Private
Sub
Workbook_Open()", located on ThisWorkbook. At present your code
is
not
firing.
When it does, you will find an error on the first line. Check
the
help
for
the Date Data Type.

Let us know if you get it to work.

NickHK

"Norbert Jaeger" wrote in message
.. .
Could please someone check my code. Something seems to be
missing.
When I open the worksheet nothing happens.


Private Sub App_WorkbookOpen()
'
If Now() 31 / 1 / 2005 Then
With Worksheets(Sheet1)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub


Regards,
Norbert

Nick,
yes, but that is not my intention. I'm lucky if I make it another
45years. :-)
I still have to set the date.

There is one tiny little thing which still makes me nervous.
After the code has run and I switch to Sheet1, I can still see the
selection of all cells active.
How can I change it, that only cell A1 is selected?

I think we gonna get a price for the longest thread for such an easy
code. Thanks to me!

Regards,
Norbert


On Thu, 3 Feb 2005 15:16:27 +0800, "NickHK"
wrote:

Norbert,
As written though, you will have to wait some 45 years to see its effect.

NickHK

"Orion" wrote in message
.. .
Nick,

this is the code which finally works to my satisfaction!
Thanks to you!!!

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() #1/31/2050# Then
With Worksheets("Sheet1").Cells
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

Best regards,
Norbert




On Thu, 3 Feb 2005 09:47:55 +0800, "NickHK"
wrote:

Norbert,
.PasteSpecial should be a method of the range object. So try this:
With Worksheets("Sheet1").Cells
.Copy
.PasteSpecial

Also if you change your date to say the year 2050, is the code still
executed ?
If so I suggest you look at the date data type in help.

NickHK

"Orion" wrote in message
.. .
Hi Nick,

thanks for your patience, man!
Now I'm getting an error message:
run time error 1004
Application-defined or object-defined error
I read through the help, but that is all greek to me.

Do you discover anything wrong in the code?
It doesn't highlight anything, so I don't know what's wrong but
the cells on Sheet1 are copied although the active sheet is Sheet3.
That I can see at the flickering border when I switch to Sheet1.
So, up to there it works. I think the .PasteSpecial command is the
problem.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() 31 / 1 / 2005 Then
With Worksheets("Sheet1")
.Cells.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

Regards,
Norbert


On Wed, 2 Feb 2005 16:25:25 +0800, "NickHK"
wrote:

Norbert,
It's because you are SELECTing the cells to work on. This is rarely
necessary in VBA as you can work on the object anyway.
Try something like:
With Worksheets("Sheet3")
.Cells.Copy
.PasteSpecial........

NickHK

"Orion" wrote in message
.. .
Nick,

thanks for your explanation. I never tried to choose something from
that list, otherwise I would have seen that one actually gets the
event from Excel automatically.

just one other thing: Assuming my Workbook has 3 worksheet and when
opening the workbook e.g. "Sheet3" is active.
How would I get my macro to go to "Sheet1", do its job and come back
to the previously active sheet, whichever it is?

I would like the user not to see, that a macro is running when he
opens the workbook. Therefor I put at the beginning of the macro
< Application.ScreenUpdating = False

At the moment the workbook opens with Sheet3 active. The macro does
its job, but on Sheet3 instead on Sheet1.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() 31 / 1 / 2005 Then
With Worksheets("Sheet1") ' this does not help!?!
Cells.Select ' it selects the cells
on Sheet3 instead Sheet1
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub





On Wed, 2 Feb 2005 15:38:24 +0800, "NickHK"
wrote:

Norbert,
In the VBA IDE, double-click the Excel object to work with, e.g.
ThisWorkbook.
At the top, left of the code pane, you'll see a combo box that has
either
"(General)" or "Workbook". Select "Workbook". At the same level, on
the
right, you now have a list of possible events for the item selected
on
the
left.

"Private Sub App_WorkbookOpen()", I assume, would be for
Application
level
events, which are slightly different

NickHK

"Orion" wrote in message
.. .
Hi Nick,

thank you very much it worked, just by using
"Private Sub Workbook_Open()" event
The date data type seems to be alright.

Thanks for your help.
Where do I know from which event to take?
I found this one
"Private Sub App_WorkbookOpen()"
somewhere in the help.

Regards,
Norbert


On Wed, 2 Feb 2005 09:39:45 +0800, "NickHK"

wrote:

Norbert,
First, you should be using the event that Excel gives you,
"Private
Sub
Workbook_Open()", located on ThisWorkbook. At present your code
is
not
firing.
When it does, you will find an error on the first line. Check
the
help
for
the Date Data Type.

Let us know if you get it to work.

NickHK

"Norbert Jaeger" wrote in message
.. .
Could please someone check my code. Something seems to be
missing.
When I open the worksheet nothing happens.


Private Sub App_WorkbookOpen()
'
If Now() 31 / 1 / 2005 Then
With Worksheets(Sheet1)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub


Regards,
Norbert