Copy paste from variable source and target to NONE continious range

[Enrix]

I have created a Userform, where you can enter a Operand (into a text box) and direction (Vertical or Horizontal) option buttons. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
When activating the macro (Command button) the user is prompted to select a range (only within one row or one column) from an open workbook (Inputbox type:=8), which is the source and then the user is prompted to select (Inputbox type:=8) the start cell for paste (target).<o></o>
<o></o>
If Operand is 2, the cells, within the source is pasted, starting with the target cell, skipping every second column or row depending if vertical or horizontal was selected.<o></o>
<o></o>
This works PERFECTLY as long as I am working within the same workbook. However, if my source is from another workbook I am NOT able to identify the workbook name from where I copied the source.<o></o>
<o></o>
In this code I have hardcoded the workbook names but I need a solution to capture the name, when the range and target is selected.<o></o>
<o></o>
This is NOT the entire code but just the part to show where the "challenge" is.<o></o>
<o></o>
Dim UserRange As Range
Dim UserStart As Range<o></o>
<o></o>
Set UserRange = Application.InputBox(Prompt:= _
"Please Select a Range", _
Title:="InputBox Method", Type:=8) ' & ActiveWorkbook.FullName


Set UserStart = Application.InputBox(Prompt:= _
"Please Select a start cell for paste", _
Title:="InputBox Method", Type:=8)<o></o>
<o></o>
Select Case True

Case obVertical

For Each r In WorkRange ' to find the first cell in selection and activate so paste start in this cell
Windows("Workbook name source").Activate ' activate the workbook from where you copy
FirstValue = r.Address
Range(FirstValue).Select
Selection.Copy
Range("workbook name target").Select
ActiveCell.Offset(rowOffset:=OffsetValueV, columnOffset:=0).Activate 'enter offset from start point to paste into second row
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

OffsetValueV = OffsetValueV + tbOperand

Next<o></o>
<o></o>
etc.<o></o>
<o></o>
<o></o>
So the problem is how to capture the workbook name since RefEdit cannot be used and the range object returned by using Inputbox do not include the workbook name ?<o></o>
<o></o>
Windows("Workbook name source").Activate <o></o>
and <o></o>
Windows("Workbook name target").Activate <o></o>
<o></o>
I appreciate any support possible.<o></o>
<o></o>
Regards<o></o>
Enrix, big Mr Excel fan<o></o>
<o></o>

 

[Comfy]

Possibly:

Dim UserRange As Range
Dim UserStart As Range
Dim wbFrom As String, wbTo As String

Set UserRange = Application.InputBox(Prompt:= _
"Please Select a Range", _
Title:="InputBox Method", Type:=8) ' & ActiveWorkbook.FullName
[B]wbFrom = UserRange.Application.ActiveWorkbook.Name & ".xls"[/B]


Set UserStart = Application.InputBox(Prompt:= _
"Please Select a start cell for paste", _
Title:="InputBox Method", Type:=8)
[B]wbTo = UserStart.Application.ActiveWorkbook.Name & ".xls"[/B]
Select Case True

Forget that, looked at it again and it didnt work

 

[Enrix]

When you select range with Inputbox the active workbook, is the workbook from where you have activated your macro (in this case userform)

So no matter where I select source and target the workbook name is the same. regards Enrix

Possibly:

Dim UserRange As Range
Dim UserStart As Range
Dim wbFrom As String, wbTo As String
 
Set UserRange = Application.InputBox(Prompt:= _
"Please Select a Range", _
Title:="InputBox Method", Type:=8) ' & ActiveWorkbook.FullName
[B]wbFrom = UserRange.Application.ActiveWorkbook.Name & ".xls"[/B]
 
 
Set UserStart = Application.InputBox(Prompt:= _
"Please Select a start cell for paste", _
Title:="InputBox Method", Type:=8)
[B]wbTo = UserStart.Application.ActiveWorkbook.Name & ".xls"[/B]
Select Case True

Forget that, looked at it again and it didnt work

 

[Enrix]

could I use range.parent.parent.name to capture the workbook name ?

When you select range with Inputbox the active workbook, is the workbook from where you have activated your macro (in this case userform)

So no matter where I select source and target the workbook name is the same. regards Enrix

 

[Comfy]

could I use range.parent.parent.name to capture the workbook name ?

Seems to work when I tested.

/Comfy

 

[Enrix]

worked like a charm, THANKS for your quick reply!

 

[Comfy]

worked like a charm, THANKS for your quick reply!

I didn't really do anything but no probs