Select a range in one one workbook while working in other workbook

Slow&Steady · #1 02-03-2010, 02:23 PM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to select all text in a presentation (even if not in outline)	smodtactical	PowerPoint	1	05-10-2010 10:11 AM
Share Workbook cannot merge	Ichigo	Excel	3	01-19-2010 03:05 AM
Select text between characters	davers	Word	10	05-24-2009 05:15 AM
How to write in Word from Select query	lakhan.p	Word	0	03-27-2009 07:25 AM
Select printer to document	JosL	Office	3	03-07-2009 12:40 PM