macro save

[puiuluipui]

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]

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]

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]

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]

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

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:


__________ 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]

It's perfect!
Thanks!

"Mike H" a scris:

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

 

[Mike H]

Your welcome

It's perfect!
Thanks!

"Mike H" a scris:

 

[puiuluipui]

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

"Roger Govier" a scris:

 

[Roger Govier]

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

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

"Roger Govier" a scris:


__________ Information from ESET Smart Security, version of virus
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) __________

The message was checked by ESET Smart Security.

http://www.eset.com