wildmanager
New Member
- Joined
- Sep 24, 2023
- Messages
- 3
- Office Version
- 2016
- 2010
- Platform
- Windows
I have been using the following for years. However I now need to use the Workbook in Shared mode, and i'm getting a 1004 error about: "Insert method of range failed."
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim wb As Workbook
Dim ws1 As Worksheet
Dim x As Integer
Dim y As Integer
Dim score As Long
Dim scoreRow As Long
Set wb = ActiveWorkbook
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Column >= 2 And Target.Column <= 10 And Target.Row >= 5 And Target.Row <= 9 Then
Set ws1 = Worksheets(1)
For x = 10 To 19
score = ws1.Cells(x, 14).Value
scoreRow = x
For y = x + 1 To 19
If ws1.Cells(y, 14).Value > score Then
score = ws1.Cells(y, 14).Value
scoreRow = y
End If
Next y
If scoreRow <> x Then
ws1.Cells(scoreRow, 13).Cut
ws1.Cells(x, 13).Insert
ws1.Cells(scoreRow, 14).Cut
ws1.Cells(x, 14).Insert
End If
ActiveWorkbook.Save
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Any help would be appreciated.
The rows where the data is 10-19 contains no other data
Next x
End If
'If Not wb.MultiUserEditing Then
'ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, accessMode:=xlShared, ConflictResolution:=xlLocalSessionChanges
ActiveWorkbook.Save
'End If
Application.DisplayAlerts = False
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim wb As Workbook
Dim ws1 As Worksheet
Dim x As Integer
Dim y As Integer
Dim score As Long
Dim scoreRow As Long
Set wb = ActiveWorkbook
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Column >= 2 And Target.Column <= 10 And Target.Row >= 5 And Target.Row <= 9 Then
Set ws1 = Worksheets(1)
For x = 10 To 19
score = ws1.Cells(x, 14).Value
scoreRow = x
For y = x + 1 To 19
If ws1.Cells(y, 14).Value > score Then
score = ws1.Cells(y, 14).Value
scoreRow = y
End If
Next y
If scoreRow <> x Then
ws1.Cells(scoreRow, 13).Cut
ws1.Cells(x, 13).Insert
ws1.Cells(scoreRow, 14).Cut
ws1.Cells(x, 14).Insert
End If
ActiveWorkbook.Save
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Any help would be appreciated.
The rows where the data is 10-19 contains no other data
Next x
End If
'If Not wb.MultiUserEditing Then
'ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, accessMode:=xlShared, ConflictResolution:=xlLocalSessionChanges
ActiveWorkbook.Save
'End If
Application.DisplayAlerts = False
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub