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