Moving rows to sequential position on another sheet

J

Jenno

Do hope someone can help me with this. I am not sure whether a functio
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 bee
entered it can then be clicked on, and a copy of that row will be pu
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 wa
required, and the second sheet would have a summary of all row
entered.

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

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
 
B

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
 
J

Jenno

Thanks for your help with this, but when I try to run this I get
syntax error. I am not familiar with Visual Basic, so not sure wha
likely problems could be. Would it be possible for you to check m
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 abou
all the fine detail. Dim or what! :confused:

Jenn
 
B

BizMark

Jenno said:
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?action=getinfo&userid=26236
View this thread:
http://www.excelforum.com/showthread.php?threadid=395283
Click to expand...


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
 
J

Jenno

Thank you so much, it worked perfectly. When I tried the first versio
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 writte
in another spreadsheet, but couldn't sort out the coding. I can follo
the logic, but that's about it.

Thanks again for your help

Jenno :
 
Top