| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro save
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro save
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" wrote: 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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro save
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: 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" wrote: 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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro save
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 "puiuluipui" wrote: 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: 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" wrote: 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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro save
Hi
The following will save every 5 rows. Switching Calculation mode to manual before saving, should speed up the process. First though, go to ToolsOptionsCalculationset to Manual and Uncheck Calculate before Save, then switch back to AutomaticOK 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" wrote in message ... 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: 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" wrote: 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! __________ 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro save
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 "puiuluipui" wrote: 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: 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" wrote: 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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro save
Your welcome
"puiuluipui" wrote: 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 "puiuluipui" wrote: 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: 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" wrote: 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! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro save
Thanks Roger. But it will not affect other calculations if i set to Manual
and Uncheck ? Thanks! "Roger Govier" a scris: Hi The following will save every 5 rows. Switching Calculation mode to manual before saving, should speed up the process. First though, go to ToolsOptionsCalculationset to Manual and Uncheck Calculate before Save, then switch back to AutomaticOK 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" wrote in message ... 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: 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" wrote: 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! __________ 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 . |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro save
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" wrote in message ... Thanks Roger. But it will not affect other calculations if i set to Manual and Uncheck ? Thanks! "Roger Govier" a scris: Hi The following will save every 5 rows. Switching Calculation mode to manual before saving, should speed up the process. First though, go to ToolsOptionsCalculationset to Manual and Uncheck Calculate before Save, then switch back to AutomaticOK 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" wrote in message ... 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: 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" wrote: 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! __________ 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 . __________ 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to Insert Current Date into cell - Macro to "Save As" | Excel Worksheet Functions | |||
| Macro Will Not Save | Excel Discussion (Misc queries) | |||
| save as macro | Excel Discussion (Misc queries) | |||
| save as macro | Excel Discussion (Misc queries) | |||
| Macro to Save without the Save Message | Excel Discussion (Misc queries) | |||
Linear Mode
