Hi there,

below find the code, which I put into the workbook_open event.
When I open my file, Sheet3 will be active
On Sheet1 the cells getting selected, copied and their values pasted
back.
Everything works fine, and after the code has ended its job, Sheet3 is
active, but when I manually change to Sheet1, I can see, that all
cells had been selected before. (They have this dark appearance, not
the flickering frame)
How do I get only cell A1 selected?
It should also happen in the background, without having to select
Sheet1 first.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() #2/3/2005# Then '#month/day/year#!!!!
With Worksheets("Sheet1").Cells
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End With
End If
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

Thanks for any advice.

Regards,
Norbert

What is the purpose of copy/paste to itself? Do the procedure without copy
like this:
With Worksheets("Sheet1").Cells
.Value = .Value
End With

Mike F

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

below find the code, which I put into the workbook_open event.
When I open my file, Sheet3 will be active
On Sheet1 the cells getting selected, copied and their values pasted
back.
Everything works fine, and after the code has ended its job, Sheet3 is
active, but when I manually change to Sheet1, I can see, that all
cells had been selected before. (They have this dark appearance, not
the flickering frame)
How do I get only cell A1 selected?
It should also happen in the background, without having to select
Sheet1 first.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() #2/3/2005# Then '#month/day/year#!!!!
With Worksheets("Sheet1").Cells
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End With
End If
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

Thanks for any advice.

Regards,
Norbert

Another option is to just select A1 of that sheet.

application.goto worksheets("sheet1").range("a1"),scroll:=true

is one way.

Orion wrote:

Hi there,

below find the code, which I put into the workbook_open event.
When I open my file, Sheet3 will be active
On Sheet1 the cells getting selected, copied and their values pasted
back.
Everything works fine, and after the code has ended its job, Sheet3 is
active, but when I manually change to Sheet1, I can see, that all
cells had been selected before. (They have this dark appearance, not
the flickering frame)
How do I get only cell A1 selected?
It should also happen in the background, without having to select
Sheet1 first.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() #2/3/2005# Then '#month/day/year#!!!!
With Worksheets("Sheet1").Cells
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End With
End If
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

Thanks for any advice.

Regards,
Norbert

--

Dave Peterson

Hi Mike,
don't I have to copy something first in order to be able to paste it?
I copy all cells (which at this present moment contain formulas) and
paste their values.

I tried your code, but my Excel seems to run in circels and I have to
kill the process.

Any other idea?

Thanks,
Norbert


On Mon, 7 Feb 2005 06:39:08 -0500, "Mike Fogleman"
wrote:

What is the purpose of copy/paste to itself? Do the procedure without copy
like this:
With Worksheets("Sheet1").Cells
.Value = .Value
End With

Mike F

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

below find the code, which I put into the workbook_open event.
When I open my file, Sheet3 will be active
On Sheet1 the cells getting selected, copied and their values pasted
back.
Everything works fine, and after the code has ended its job, Sheet3 is
active, but when I manually change to Sheet1, I can see, that all
cells had been selected before. (They have this dark appearance, not
the flickering frame)
How do I get only cell A1 selected?
It should also happen in the background, without having to select
Sheet1 first.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() #2/3/2005# Then '#month/day/year#!!!!
With Worksheets("Sheet1").Cells
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End With
End If
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

Thanks for any advice.

Regards,
Norbert

Hi Dave,

this would work nicely but I don't want to 'goto' Sheet1. It should
happen in the background.

Thanks,
Norbert

On Mon, 07 Feb 2005 19:18:01 -0600, Dave Peterson
wrote:

Another option is to just select A1 of that sheet.

application.goto worksheets("sheet1").range("a1"),scroll:=true

is one way.

Orion wrote:

Hi there,

below find the code, which I put into the workbook_open event.
When I open my file, Sheet3 will be active
On Sheet1 the cells getting selected, copied and their values pasted
back.
Everything works fine, and after the code has ended its job, Sheet3 is
active, but when I manually change to Sheet1, I can see, that all
cells had been selected before. (They have this dark appearance, not
the flickering frame)
How do I get only cell A1 selected?
It should also happen in the background, without having to select
Sheet1 first.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() #2/3/2005# Then '#month/day/year#!!!!
With Worksheets("Sheet1").Cells
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End With
End If
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

Thanks for any advice.

Regards,
Norbert

Turn off screenupdating, goto a cell (to select it) and then go back to where
you want to be. Then turn on screenupdating.

Orion wrote:

Hi Dave,

this would work nicely but I don't want to 'goto' Sheet1. It should
happen in the background.

Thanks,
Norbert

On Mon, 07 Feb 2005 19:18:01 -0600, Dave Peterson
wrote:

Another option is to just select A1 of that sheet.

application.goto worksheets("sheet1").range("a1"),scroll:=true

is one way.

Orion wrote:

Hi there,

below find the code, which I put into the workbook_open event.
When I open my file, Sheet3 will be active
On Sheet1 the cells getting selected, copied and their values pasted
back.
Everything works fine, and after the code has ended its job, Sheet3 is
active, but when I manually change to Sheet1, I can see, that all
cells had been selected before. (They have this dark appearance, not
the flickering frame)
How do I get only cell A1 selected?
It should also happen in the background, without having to select
Sheet1 first.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() #2/3/2005# Then '#month/day/year#!!!!
With Worksheets("Sheet1").Cells
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End With
End If
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

Thanks for any advice.

Regards,
Norbert

--

Dave Peterson

This is what I want:
The users of my spreadsheets are not very advanced, so I do not look
for a supersophisticated solution, I just want the code to delete the
formulas of a certain worksheet and replace them with their values on
a certain date.
The user, when he/she opens the workbook, must not see what's going
on, therefor the spreadsheet should open as it has been saved (I don't
know how they save it, which Sheet is active ...) therefor all the
changes must be done to Sheet1 although the user doesn't see it.

I can't put a command into the code, selecting A1 on Sheet1 and go
back, because I don't know where that is, it could be Sheet3 or Sheet4
or everytime somewhere else, on which ever Sheet the user was busy
when saving the workbook.

If there is a command which does this:
select A1 on Sheet1 and go back to wherever you came from, that would
help.

Thanks,
Norbert





On Tue, 08 Feb 2005 19:54:23 -0600, Dave Peterson
wrote:

Turn off screenupdating, goto a cell (to select it) and then go back to where
you want to be. Then turn on screenupdating.

Orion wrote:

Hi Dave,

this would work nicely but I don't want to 'goto' Sheet1. It should
happen in the background.

Thanks,
Norbert

On Mon, 07 Feb 2005 19:18:01 -0600, Dave Peterson
wrote:

Another option is to just select A1 of that sheet.

application.goto worksheets("sheet1").range("a1"),scroll:=true

is one way.

Orion wrote:

Hi there,

below find the code, which I put into the workbook_open event.
When I open my file, Sheet3 will be active
On Sheet1 the cells getting selected, copied and their values pasted
back.
Everything works fine, and after the code has ended its job, Sheet3 is
active, but when I manually change to Sheet1, I can see, that all
cells had been selected before. (They have this dark appearance, not
the flickering frame)
How do I get only cell A1 selected?
It should also happen in the background, without having to select
Sheet1 first.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() #2/3/2005# Then '#month/day/year#!!!!
With Worksheets("Sheet1").Cells
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End With
End If
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

Thanks for any advice.

Regards,
Norbert

You could keep track of where you are, go off and come back.

dim curSel as range
dim ActCell as range

set cursel = selection
set actcell = activecell

application.goto worksheets("sheet1").range("a1"), scroll:=true

application.goto cursel
actcell.activate





Orion wrote:

This is what I want:
The users of my spreadsheets are not very advanced, so I do not look
for a supersophisticated solution, I just want the code to delete the
formulas of a certain worksheet and replace them with their values on
a certain date.
The user, when he/she opens the workbook, must not see what's going
on, therefor the spreadsheet should open as it has been saved (I don't
know how they save it, which Sheet is active ...) therefor all the
changes must be done to Sheet1 although the user doesn't see it.

I can't put a command into the code, selecting A1 on Sheet1 and go
back, because I don't know where that is, it could be Sheet3 or Sheet4
or everytime somewhere else, on which ever Sheet the user was busy
when saving the workbook.

If there is a command which does this:
select A1 on Sheet1 and go back to wherever you came from, that would
help.

Thanks,
Norbert

On Tue, 08 Feb 2005 19:54:23 -0600, Dave Peterson
wrote:

Turn off screenupdating, goto a cell (to select it) and then go back to where
you want to be. Then turn on screenupdating.

Orion wrote:

Hi Dave,

this would work nicely but I don't want to 'goto' Sheet1. It should
happen in the background.

Thanks,
Norbert

On Mon, 07 Feb 2005 19:18:01 -0600, Dave Peterson
wrote:

Another option is to just select A1 of that sheet.

application.goto worksheets("sheet1").range("a1"),scroll:=true

is one way.

Orion wrote:

Hi there,

below find the code, which I put into the workbook_open event.
When I open my file, Sheet3 will be active
On Sheet1 the cells getting selected, copied and their values pasted
back.
Everything works fine, and after the code has ended its job, Sheet3 is
active, but when I manually change to Sheet1, I can see, that all
cells had been selected before. (They have this dark appearance, not
the flickering frame)
How do I get only cell A1 selected?
It should also happen in the background, without having to select
Sheet1 first.

Private Sub Workbook_Open()
'
Application.ScreenUpdating = False
If Now() #2/3/2005# Then '#month/day/year#!!!!
With Worksheets("Sheet1").Cells
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End With
End If
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

Thanks for any advice.

Regards,
Norbert

--

Dave Peterson

Dave,

you are a star!!!

it works 100%

Thank you very much, I appreciate it.

Regards,
Norbert

On Wed, 09 Feb 2005 19:24:51 -0600, Dave Peterson
wrote:

dim curSel as range
dim ActCell as range

set cursel = selection
set actcell = activecell

application.goto worksheets("sheet1").range("a1"), scroll:=true

application.goto cursel
actcell.activate