macro save

puiuluipui

unread,

Jan 4, 2010, 8:52:01 AM1/4/10

to

Hi, i have this macro that save entire workbook at row change.
But this macro is saving entire workbook and it's taking too much everytime
i change row.
Can this macro be made to save only active sheet?

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Static lngRow As Long
If Target.Row <> lngRow Then Me.save
lngRow = Target.Row
End Sub

Can this be done?
Thanks!

Mike H

unread,

Jan 4, 2010, 9:00:01 AM1/4/10

to

Hi,

> Can this macro be made to save only active sheet?

No. That's not an option in Excel. So if your finding it necessary to save
every time you change row!! then it's the entire workbook. Have you
considered saving less frequently?

Mike

puiuluipui

unread,

Jan 4, 2010, 9:09:01 AM1/4/10

to

It did cross my mind to save every 5 rows. But it will take also too much
time. But it's an option.
Can this code be made to save every 5 rows?
Thanks!

"Mike H" a scris:

Mike H

unread,

Jan 4, 2010, 9:35:01 AM1/4/10

to

Hi,

You can do this which now saves every 5 changes of row.

Increase this number to reduce the frequency of save
If TimesSaved > 4 Then

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Static lngRow As Long

Static TimesSaved As Long
If Target.Row <> lngRow Then
TimesSaved = TimesSaved + 1
End If
If TimesSaved > 4 Then
Me.Save
TimesSaved = 0
End If

lngRow = Target.Row
End Sub

Mike

Roger Govier

unread,

Jan 4, 2010, 9:53:41 AM1/4/10

to

Hi

The following will save every 5 rows.
Switching Calculation mode to manual before saving, should speed up the
process.
First though, go to Tools>Options>Calculation>set to Manual and Uncheck
Calculate before Save, then switch back to Automatic>OK

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static lngRow As Long
If lngRow < 5 Then lngRow = 5
If Target.Row = lngRow Then
Application.Calculation = xlCalculationManual
ThisWorkbook.Save
Application.Calculation = xlCalculationAutomatic
lngRow = lngRow + 5
End If
End Sub

--
Regards
Roger Govier

"puiuluipui" <puiul...@discussions.microsoft.com> wrote in message
news:3537253D-F195-414B...@microsoft.com...

> __________ Information from ESET Smart Security, version of virus
> signature database 4742 (20100104) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>

__________ Information from ESET Smart Security, version of virus signature database 4742 (20100104) __________

The message was checked by ESET Smart Security.

http://www.eset.com

puiuluipui

unread,

Jan 4, 2010, 10:07:01 AM1/4/10

to

It's perfect!

Mike H

unread,

Jan 4, 2010, 10:09:01 AM1/4/10

to

Your welcome

puiuluipui

unread,

Jan 5, 2010, 2:49:01 AM1/5/10

to

Thanks Roger. But it will not affect other calculations if i set to Manual
and Uncheck ?
Thanks!

"Roger Govier" a scris:

> .
>

Roger Govier

unread,

Jan 5, 2010, 6:31:14 AM1/5/10

to

Hi

No, as I said, after unchecking, switch back to Automatic before clicking
OK.
Calculations will occur automatically as they do at present.
The code will switch calculation mode to Manual, just to speed up the Save
(as you said it was very slow), and then will switch back to Automatic again
immediately after the Save has been made.

--
Regards
Roger Govier

"puiuluipui" <puiul...@discussions.microsoft.com> wrote in message

news:017B1CA8-70E8-4B75...@microsoft.com...

> signature database 4744 (20100105) __________

>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>

__________ Information from ESET Smart Security, version of virus signature database 4744 (20100105) __________