Copy data to user selected cell in another workbook?

Question

I am fairly new to VBA I figured out how to copy a cell value from one workbook to another assuming the location of the destination cell is known.

What i need is for the user to select a single cell in a worksheet and when a command button attached to a macro is clicked data from a fixed location is copied to that cell.

I would really appreciate any suggestions...

I need to open a new workbook and copy cell / named range then come back to original workbook and paste in the location that was selected by the user. In case of range the selected cell is taken as starting cell.

Have you tried using ActiveCell.Copy instead of Range("XXX").Copy? — assylias
Actually i know where the data is. Its the cell where it needs to be copied that will be selected by the user. — Deepak
ActiveCell.Paste then?! That's assuming the button in on the destination sheet. — assylias
I thought that would work but since i open another workbook to collect the info.. ThisWorkbook.Worksheets(usname).Range("xxx").PasteSpecial works but ThisWorkbook.Worksheets(usname).ActiveCell.PasteSpecial gives subscript out of range error — Deepak
if they click you button after selecting the cell, then you can grab the location using Activecell.Address and put that into your paste: ThisWorkbook.Worksheets(usname).Range(SavedCellAddress).PasteSpecial — SeanC

Deepak Deepak · Accepted Answer · 2012-06-02T05:30:57

I used following method to solve my problem..

I passed the row & column index to the function that handles the copying To get the cell index

Activecell.row
Activecell.column

Then pasted to that location as follows...

ThisWorkbook.Worksheets(<worksheetname>).Cells(row,column).pastespecial

Copy data to user selected cell in another workbook?

1 Answers