Excel will not recognise Macro

  • Thread starter Thread starter Mark Wright
  • Start date Start date
M

Mark Wright

Ladies & Gentlemen,
A small problem which is either me being stupid or Excel.

I have borrowed some code from David McRichie's site which I have then
pasted into a Module in the VBA Editor window. When I click on the Run
button to run the code Excel brings up the Macros dialog box asking me to
name and create a new macro, in other words it is not recognising my module.
The problem seems to be the argument "ByVal Target As Range". Excel has no
problem recognising the macro without the argument, but this means that the
macro doesn't work.
The code is as below.

"Private Sub Worksheet_Change(ByVal Target As Range)
Dim newSht As String, oldSht As String
Dim wsOld As Worksheet, wsNew As Worksheet
If Target.Column <> 6 Or Target.Row = 1 Then Exit Sub
oldSht = ActiveSheet.Name
Set wsNew = ActiveSheet
newSht = Target.Text
On Error Resume Next
Sheets(newSht).Activate
If Err.Number = 0 Then 'sheetname already exists
Sheets(oldSht).Activate
Exit Sub
End If
On Error Resume Next
'Create New Sheet
Sheets.Add After:=Sheets(Sheets.Count) '-- place at end
ActiveSheet.Name = newSht
Set wsNew = ActiveSheet
wsNew.Cells(1, 1) = "'" & newSht 'name of new sheet into cell
' Sheets(Sheets.Count).Activate 'try to show last tab
Sheets(oldSht).Activate
End Sub"

Many thanks in anticipation
Mark Wright
 
This code is a worksheet_change event so it will ONLY work in a worksheet
module. It is NOT designed to be clicked but to change automatically when a
non-restricted cell is changed.
If Target.Column <> 6 Or Target.Row = 1 Then Exit Sub
in this case it will only work in col 6 below row 1
 
Back
Top