Hi Friends,

This is my first thread to this community.
Hope all is well

letme come to the point
I am selecting a range of cells from one sheet , copy it and paste link in the temproary sheet named "Transpose_Temp" replacing "=" by "#="
and copy it and Select a final destination cell from a sheet in the same workbook and pastespecial + transpose it ,replacing "#=" by '="
This works fine within the workbook.
Not working when i select the destination cells from an another workbook ..
Full code is given below

Sub trans()
Dim sheetname, Dest_Sheet As String
Dim rng,rng1 As Range
Set rng = Application.InputBox("Select cell(s) you want to transpose and Link ", Type:=8)
Set rng1 = Application.InputBox("Select the cell where you want the final transposed and linked range to reside", Type:=8)
rng.Copy
Sheets.Add Type:=xlWorksheet
ActiveSheet.Name = "Transpose_Temp"
sheetname = "Transpose_Temp"
ActiveSheet.Paste Link:=True
Selection.Replace What:="=", Replacement:="#=", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Selection.Copy
rng1.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Dest_Sheet = rng1.Parent.Name
Sheets(Dest_Sheet).Activate
Selection.Replace What:="#=", Replacement:="=", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Sheets(sheetname).Delete
End Sub


Appreciate any help !!!!!
Thanks in advance,
Sri