Moving rows to sequential position on another sheet

[Jenno]

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?

[FSt1]

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
>
>

[BizMark]

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.Rows.Count

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


--
BizMark

[Jenno]

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!

Jenno

[BizMark]

Jenno Wrote:
> 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!
>
> 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


--
BizMark

[Jenno]

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