Getting data from another worksheet

[Paul]

Hi,
I have an ever expanding excel sheet listing transactions. New ones are
added to the bottom of the list. In the row cells will show name, address,
value etc etc.
I want to have another worksheet that picks up the name, address, value etc
and put it into a layout that will be used to authorise the transaction.
In this new sheet I could use "=spreadsheet name A 121" to pick up the data
in cell A121. However, that would mean setting up new formulae every time a
new transaction went onto the original sheet. I want to be able to enter the
row number,121, once in the new spreadsheet, in cell A1 say, and then have
the spreadsheet pick up all the relevant cells on row 121. When a new
transaction went on I would simply key in 122 in cell A1 and it would produce
all the data for row 122.

Thanks for looking, any good ideas would be much appreciated.

Paul

 

[Bruno Campanini]

Hi,
I have an ever expanding excel sheet listing transactions. New ones are
added to the bottom of the list. In the row cells will show name, address,
value etc etc.
I want to have another worksheet that picks up the name, address, value
etc
and put it into a layout that will be used to authorise the transaction.
In this new sheet I could use "=spreadsheet name A 121" to pick up the
data
in cell A121. However, that would mean setting up new formulae every time
a
new transaction went onto the original sheet. I want to be able to enter
the
row number,121, once in the new spreadsheet, in cell A1 say, and then have
the spreadsheet pick up all the relevant cells on row 121. When a new
transaction went on I would simply key in 122 in cell A1 and it would
produce
all the data for row 122.

Thanks for looking, any good ideas would be much appreciated.

Paul

Try this:
=====================================
Sub Button45_Click()
Dim SourceRange As Range, TargetRange As Range
Dim LastWrittenRow As Range, RowToCopy

' Definitions
' --------------------------------
Set SourceRange = [Sheet10!A1]
Set TargetRange = [Sheet2!A280]
' --------------------------------

RowToCopy = InputBox("Row To Copy")
If Not IsNumeric(RowToCopy) Then Exit Sub
Set LastWrittenRow = TargetRange.End(xlDown)

' Copies entire row
'SourceRange.Offset(RowToCopy - 1, 0).EntireRow.Copy
'TargetRange.Offset(LastWrittenRow.Row - TargetRange.Row + 1, 0). _
PasteSpecial xlPasteValues

' Copies one cell only
TargetRange.Offset(LastWrittenRow.Row - TargetRange.Row + 1, 0) = _
SourceRange.Offset(RowToCopy - 1, 0)

End Sub
==========================

Ciao
Bruno