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)
Code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case "$A$5"
Sheets("Sheet3").Select
Range("A34").Select
Selection.copy
Sheets("front").Select
Range("F13").Select
ActiveSheet.Paste
Range("F15").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("F17").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Select
End Sub
Display More
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.