macro save

P

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!
 
M

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
 
P

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:
 
M

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
 
R

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

puiuluipui said:
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
 
P

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
 
P

puiuluipui

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

"Roger Govier" a scris:
 
R

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

puiuluipui said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top