how to get rid of selection of cells, after pasting

[Orion]

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

[mangesh_yadav]

After all your operations are over, just before you end the sub, add the line:
Worksheets("Sheet1").Range("A1").Select

- Mangesh

[Mike Fogleman]

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" <[email protected]> wrote in message
news:[email protected]...
> 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]

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

[Orion]

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
<[email protected]> 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

[Orion]

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"
<[email protected]> 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" <[email protected]> wrote in message
>news:[email protected]...
>> 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]

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
> <[email protected]> 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

[Orion]

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
<[email protected]> 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
>> <[email protected]> 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

[mangesh_yadav]

Use the following macro instead:


Private Sub Workbook_Open()

Application.ScreenUpdating = False

Shtname = ActiveSheet.Name

Worksheets("Sheet1").Cells.Copy
Worksheets("Sheet1").Cells.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("A1").Select

Worksheets(Shtname).Select

Application.ScreenUpdating = True

End Sub


- Mangesh

[Dave Peterson]

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
> <[email protected]> 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
> >> <[email protected]> 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

[Orion]

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
<[email protected]> 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

[Dave Peterson]

Glad you got it working.

<<snipped>>