01-02-2020 08:01 AM
01-02-2020 08:01 AM
Hello All, I have two pieces of code.
The first is a "select and move" piece of code to take a line from one worksheet and transfer it to another worksheet dependent on cell content, it also unlocks and then locks the worksheet to perform this task.
The second piece of code I have copied from an online post which is a "save and close" piece of code that will close the spreadsheet after 1 minute of inactivity as I cannot use the first piece of code in "shared" mode.
Both codes works fine on their own, but when I try to combine the two the "auto close" piece of code throws up errors. How do I successfully combine the two codes to run consecutively? Sorry very new to coding do not have the knowledge to resolve this myself, I have read and tried multiple ideas on here but with no success.
I have pasted the code I am using below:
Worksheet 1 Code - is the code on worksheet 1
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns(12)) Is Nothing Then Exit Sub If Target.Count > 1 Then Exit Sub Sheet2.Unprotect "trial1" Application.ScreenUpdating = False If Target.Value = "Yes" Then Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2) Target.EntireRow.Delete End If Sheet2.Protect "trial1" Application.ScreenUpdating = True End Sub CloseTime As Date Sub TimeSetting() CloseTime = Now + TimeValue("00:01:00") On Error Resume Next Application.OnTime EarliestTime:=CloseTime, _ Procedure:="SavedAndClose", Schedule:=True End Sub Sub TimeStop() On Error Resume Next Application.OnTime EarliestTime:=CloseTime, _ Procedure:="SavedAndClose", Schedule:=False End Sub Sub SavedAndClose() ActiveWorkbook.Close Savechanges:=True End Sub
THISWORKBOOK CODE - is the code from the "Thisworkbook" module
Private Sub Workbook_Open() StartTimer End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) StartTimer End Sub
THISWORKBOOK CODE - Module - is the code I have entered the module created fro "Thisworkbook"
Const idleTime = 60 'seconds Dim Start Sub StartTimer() Start = Timer Do While Timer < Start + idleTime DoEvents Loop Application.DisplayAlerts = False ActiveWorkbook.Close True Application.DisplayAlerts = True End Sub
Any help you can give is greatly appreciated?
01-03-2020 02:11 AM