Moving rows to sequential position on another sheet
 

Do hope someone can help me with this. I am not sure whether a function
would do the job, or whether it requires a macro.

I need to be able to have a button set up so that when a row has been
entered it can then be clicked on, and a copy of that row will be put
on another sheet.

The rows on the second sheet should be in sequence order - latest last.
The row on the first sheet would be overwritten each time an entry was
required, and the second sheet would have a summary of all rows
entered.

Hope that's clear, does *Offset* come into the solution somewhere?


--
Jenno
------------------------------------------------------------------------
Jenno's Profile: http://www.excelforum.com/member.php...o&userid=26236
View this thread: http://www.excelforum.com/showthread...hreadid=395283

hi,
assumption:
1.data will be entered on row2 sheet1
2.Data will be transfers to sheet2
3.the world is not flat

Rows("2:2").Copy Destination:= _
Sheets("Sheet1").Range("A65000").End(xlUp).Offset( 1, 0)

edit to fit your data.

regards
FSt1
"Jenno" wrote:


Do hope someone can help me with this. I am not sure whether a function
would do the job, or whether it requires a macro.

I need to be able to have a button set up so that when a row has been
entered it can then be clicked on, and a copy of that row will be put
on another sheet.

The rows on the second sheet should be in sequence order - latest last.
The row on the first sheet would be overwritten each time an entry was
required, and the second sheet would have a summary of all rows
entered.

Hope that's clear, does *Offset* come into the solution somewhere?


--
Jenno
------------------------------------------------------------------------
Jenno's Profile: http://www.excelforum.com/member.php...o&userid=26236
View this thread: http://www.excelforum.com/showthread...hreadid=395283

Well, let's assume that your data entry row is in Sheet1, in Row 2 (to allow headings in Row 1) and that your summary is to go in Sheet2,

You could attach a macro like this to a button next to the entry row:

Sub CopyDataRow
nInputRow = 2
nOutputRowStarts = 2

nRowsLogged = Sheet2.Cells(nOutputRowStarts,1).CurrentRegion.Row s.Count

for each xCell in Sheet1.Rows(nInputRow).Cells
Sheet2.Cells(nRowsLogged + 1,xCell.Column) = xCell.value
next xCell
End Sub

Thanks for your help with this, but when I try to run this I get a
syntax error. I am not familiar with Visual Basic, so not sure what
likely problems could be. Would it be possible for you to check my
coding - copied below

Sub CopyDataRow()
nInputRow = 2
nOutputRowStarts = 2

nRowsLogged =
Sheet2.Cells(nOutputRowStarts, 1).CurrentRegion.Rows.Count

For Each xCell In Sheet1.Rows(nInputRow).Cells
Sheet2.Cells(nRowsLogged + 1, xCell.Column) = xCell.Value
Next xCell




End Sub

Is that all coding or is any of it a comment, as I say not sure about
all the fine detail. Dim or what! :confused:

Jenno


--
Jenno
------------------------------------------------------------------------
Jenno's Profile: http://www.excelforum.com/member.php...o&userid=26236
View this thread: http://www.excelforum.com/showthread...hreadid=395283

Hmmm. I tried copying and pasting it into a module (I admit I just wrote it straight into the forum, I didn't test it) and it worked fine for me.
One thing though is that it assumed that the Headings were already in Row 2.
I tightened up the code to make sure this happens and also copies the cells in one hit, as follows:

Sub CopyDataRow()
nInputRow = 2
nOutputRowStarts = 2

If Range("Sheet2!A1").Text = "" Then
Sheet2.Rows(1).EntireRow.Cells.Value = _
Sheet1.Rows(1).EntireRow.Cells.Value
End If
nRowsLogged = Sheet2.Cells(1, 1).CurrentRegion.Rows.Count

Sheet2.Rows(nRowsLogged + 1).EntireRow.Cells.Value = _
Sheet1.Rows(nInputRow).Cells.Value
End Sub




Regards,
BizMark

Thank you so much, it worked perfectly. When I tried the first version
I admit I probably didn't have any headings on the 1st row.

I had spent ages fiddling about with a macro someone else had written
in another spreadsheet, but couldn't sort out the coding. I can follow
the logic, but that's about it.

Thanks again for your help

Jenno :)


--
Jenno
------------------------------------------------------------------------
Jenno's Profile: http://www.excelforum.com/member.php...o&userid=26236
View this thread: http://www.excelforum.com/showthread...hreadid=395283