selecting current row information and transfering it.

[Guest]

I have one workbook sheet with a price list and associated dat in rows. I
need to be able to double click or similar on a cell containeing the pirce of
a particular model have this action select all the associated data in that
row and enter this detail in a series of cells in another workbook/worksheet.

Any suggestions on how to code this please?

 

[Tom Ogilvy]

right click on the tab of the sheet with the data, select view code, thenput
in data like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Set rng = Workbooks("Mybook2.xls") _
.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp)(2)
Target.EntireRow.Copy rng
Cancel = True
End Sub

The challenge is how to communicate which workbook/cell to copy the
information to.

You haven't provided enough information to determine that.

 

[Guest]

Hi Tom,
Sorry need some more help on this one!

The sorce data is in workbook ("Pricing.xls").Worksheet("List") . The
complete row of data needs to be copied from here. and then the the target
workbook is called Offer.xls and the worksheet is "Offer page". The cells in
the row must be added to a number of non-adjacent cells. e.g.

Row double clicked in Pricing.xls. List is paster as follows.

cell 1 in row to cell B6 in ("Offer.xls").(" Offer page")

cell3 in row to cell B10 on ("Offer.xls").(" Offer page")

Hope this makes sense

 

[Tom Ogilvy]

Code goes in [Pricing.xls]List code module

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
dim rng as Range, rng1 as Range
Set rng = Workbooks("Offer.xls") _
.Worksheets("Offer page").Range("B6")
Set rng1 = Workbooks("Offer.xls") _
.Worksheets("Offer page").Range("B10")
rng.Value = Cells(target.row,1).Value
rng1.Value = cells(Target.row,3).Value

Cancel = True
End Sub