Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-03-2010, 02:23 PM
Slow&Steady Slow&Steady is offline Select  a range in one one workbook while working in other workbook Windows XP Select  a range in one one workbook while working in other workbook Office 2003
Novice
Select  a range in one one workbook while working in other workbook
 
Join Date: Feb 2010
Posts: 1
Slow&Steady is on a distinguished road
Post Select a range in one one workbook while working in other workbook

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
Reply With Quote
  #2  
Old 02-21-2010, 03:34 AM
BjornS BjornS is offline Select  a range in one one workbook while working in other workbook Windows Vista Select  a range in one one workbook while working in other workbook Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

Hello Sri,
I guess i know what is the problem, but I can't try it, because I am really a beginner at macros (I am more a formula-fan).

I think you always have to create the sheet "Transpose_temp" in the same sheet as the destination.

Check it out and give. It would be interesting to hear if it works or not.

Kind regards
Bjorn
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select  a range in one one workbook while working in other workbook 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  a range in one one workbook while working in other workbook Select printer to document JosL Office 3 03-07-2009 12:40 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:21 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft