Can VBA know where I click?

[EOAEvan]

I have a spreadsheet that the layout does not change. For this example lets say I have a range A1:A25. That range ends up getting pasted into another workbook into a specific column. The issue I am running into is that depending on several factors the column the info gets pasted into will change. Is it possible for a macro to copy the range A1:A25 and begin pasting it to the column I click in the other workbook... I think what I would need is for it to DIM the filename & column I click and then use that in the rest of the code. Is that possible?

 

[sahild05]

dim b as range
b = application.inputbox("Select destination range", "Select Range", Type:=8)

 

[EOAEvan]

interesting.. This might be in the right direction however Im finding it difficult to alter it to what I want.

Here is some code I use to copy/paste as values from one sheet to another within the same workbook.

Sub FlashHardCode()

frow = Array(9, 15, 21, 26, 39, 44, 49, 52, 57, 60, 63, 68, 70, 75, 79, 82, 85, 90, 93, 96, 101, 104, 107, 112, 127, 133, 137, 144, 146)
trow = Array(13, 19, 24, 32, 39, 44, 50, 53, 58, 61, 66, 68, 73, 77, 80, 83, 88, 91, 94, 99, 102, 105, 110, 122, 128, 134, 139, 144, 147)
For i = LBound(frow) To UBound(frow)
    Sheets("Flash").Range("D" & frow(i) & ":E" & trow(i)).Value = Sheets("Flash Linked").Range("D" & frow(i) & ":E" & trow(i)).Value
Next i

End Sub

How could I incorporate your code with this? So rather than having to select the range on the other workbook I just want it to DIM the workbook name and the column. The ranges it will copy from and to will not change. Only the destination column.

maybe something like

Workbooks(A).Sheets(B).Range(C & frow(i) & C & trow(i)).Value = Sheets("Flash Linked").Range("D" & frow(i) & ":D" & trow(i)).Value

where A is the workbook, B is the sheet I selected and C is the column I selected.

 

[EOAEvan]

bump:... Anyone?