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)
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
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.