macro track changes return error #6 overflow / #13 type mismatch

[kelvin_9]

hello, i'm a macro beginner and i've got an error #6/#13 on this 2 code, what's wrong with this and how can i amend it?
thank you so much

error #13 type mismatch (select more than 1 cell)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)Dim sSheetName As String
sSheetName = ActiveSheet.Name
If ActiveSheet.Name <> "TRACK" Then
Application.EnableEvents = False
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & " – " & Target.Address(0, 0)
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
'Sheets("TRACK").Hyperlinks.Add Anchor:=Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="‘" & sSheetName & "‘!" & oldAddress, TextToDisplay:=oldAddress


'Sheets("TRACK").Columns("A:D").AutoFit
Application.EnableEvents = True
End If
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
oldValue = Target.Value
oldAddress = Target.Address
End Sub

error #6 overflow (slecet the whole sheet)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)Dim sSheetName As String
sSheetName = ActiveSheet.Name
If ActiveSheet.Name <> "TRACK" Then
Application.EnableEvents = False
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & " – " & Target.Address(0, 0)
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
'Sheets("TRACK").Hyperlinks.Add Anchor:=Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="‘" & sSheetName & "‘!" & oldAddress, TextToDisplay:=oldAddress


'Sheets("TRACK").Columns("A:D").AutoFit
Application.EnableEvents = True
End If
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Count = 1 Then
oldValue = Target.Value
End If
End Sub

 

[kelvin_9]

You're code is designed for changing/pasting individual cells, not entire sheets.
To prevent the error when pasting an entire sheet use

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSheetName As String
[COLOR=#ff0000]If Target.CountLarge > 1 Then Exit Sub[/COLOR]
sSheetName = ActiveSheet.Name
If ActiveSheet.Name <> "TRACK" Then
Application.EnableEvents = False
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & " – " & Target.Address(0, 0)
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Oldvalue
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
'Sheets("TRACK").Hyperlinks.Add Anchor:=Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="‘" & sSheetName & "‘!" & oldAddress, TextToDisplay:=oldAddress


'Sheets("TRACK").Columns("A:D").AutoFit
Application.EnableEvents = True
End If
End Sub

superb cool, thanks a lot Fluff

 

[Fluff]

You're welcome & thanks for the feedback