Auto back up

komobu

New Member
Joined
Feb 7, 2011
Messages
37
There are a couple of macros that I saw online that I would really like to put in my workbook. but I am having difficulty.

The first is to automatically save a copy of my workbook to a backup on my network attached Storage. The code is as follows:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Saves the current file to a backup folder and the default folder
'Note that any backup is overwritten
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs FileName:="Y:\Excel Back Up\" & _
ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub

This works great providing the file is not already in the backup folder. So the first time I run it, it works. If I open the file and run it a second time, it crashes because a file by that name is already there.

It would actually work better for me if I could add the date and time to the filename. So if my original file name is "widgets" I would like by back up filename to be "Widgets Sep 6,2020 6:05pm" This way no two files will ever have the same name, and if I run into problems, I can go back as far as I like.

How can I add the date and time to the ActiveWorkbook.Name?




The second thing I am trying to get working is the following:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'Select the entire row and column of the selected cell
' Disable events to prevent recursion
Application.EnableEvents = False

With Target
Union(.EntireRow, .EntireColumn).Select
.Activate
End With

Application.EnableEvents = True

End Sub

As is, this code works fine. It will highlight the entire row and the entire column I am in. I like it because if my often I have to add stuff in column w or ab for a line entry in column a.

What I would really like though is just to have the row highlighted and not the column. I tried replacing "
Union(.EntireRow, .EntireColumn).Select.Activate" with ".EntireRow.Select" then the problem is it will always put me in the first column. So if I select cell "T15" for example, it will highlight the whole row of 15, but it will put me in cell A15. So there is no way to edit cell T15.

Is there anyway to Select the entire row, but to remain in a different column then A?

If there isnt, is it possible to change the color for what ever row I am in to say green, and then when I leave that row, it will revert back to what ever color it was previously?

Thanks for any help with these macros
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I was able to figure out the second question I had about highlighting the row I am on with the following:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
With Target
.EntireRow.Interior.ColorIndex = 40

End With
Application.ScreenUpdating = True
End Sub


Still need help with the autoback up though
 
Upvote 0
.
Save date and time :

VBA Code:
 ActiveWorkbook.SaveCopyAs FileName:="Y:\Excel Back Up\"  & Format(Date, "ddmmmyyyy") & "_" & _
     Format(Time, "hAM/PM") & ".xlsm"
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top