VBA on case select recorded code not working

  • Ok I seem to be going around in circles, I have tried numerous ways of getting this to work and it just wont. All I need to work out is how to have a value from one page copied to another when a cell is selected (I will try and incorporate a hyperlink into the code as well)



    I recorded the code above (between "case"$A$5"" and "end select") but it reaches the first range select and drops out with "select method of range class failed" even though the code works if run as a macro.


    I have also tried with worksheets("worksheetname") the copying the range but that also will not work for me.


    Please help.

  • I have only made the assumption that you are wanting to change the Numberformat in F17... you can amend this to some other sort of format if you like though.... but this is how I would do it.


    [vba]Option Explicit


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rngSrc As Range
    If Not Application.Intersect(Target, Range("A5")) Is Nothing Then
    Set rngSrc = Sheets("Sheet3").Range("A34")
    rngSrc.Copy Sheets("front").Range("F13")
    With Sheets("front")
    .Range("F15").Value = rngSrc.Value
    .Range("F17").NumberFormat = rngSrc.NumberFormat
    End With
    End If
    Set rngSrc = Nothing
    End Sub[/vba]