I am a metrologist and use an Excel spreadsheet to enter
six data items that calculate various weight corrections,
air density, etc. I then need to transfer 5 data items
from that spreadsheet to another sheet in the same
workbook aligned in a row. After that, I go back to the
main spreadsheet and enter new data for the next weight I
test and do the same thing, except that the new set of
data should be in the row immediately underneath the
previous row.

I have recorded a macro which does that, using copy, then
paste, for the 5 items. However when I enter the next set
of data and hit the transfer macro, it just writes over
the first set of data.

How can I get it to skip a line and enter a new line of
data? After I transfer all the data I copy it into a
Word document that has our labs header.

TIA
Steve

Steve,

First calculate the last used row on the second sheet

cLastRow = Worksheet("Sheet2").Cells(Rows.Count,"A").End(xlUp ).Row

then change the paste code to use that value +1. Foir instance, if your code
looks like

Ramge("A1:A5").Copy Destination:=Worksheets("Sheet2").Range("A1")

change this to

Ramge("A1:A5").Copy Destination:=Worksheets("Sheet2").Range("A" &
cLastRow+1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steve Schultz" wrote in message
...
I am a metrologist and use an Excel spreadsheet to enter
six data items that calculate various weight corrections,
air density, etc. I then need to transfer 5 data items
from that spreadsheet to another sheet in the same
workbook aligned in a row. After that, I go back to the
main spreadsheet and enter new data for the next weight I
test and do the same thing, except that the new set of
data should be in the row immediately underneath the
previous row.

I have recorded a macro which does that, using copy, then
paste, for the 5 items. However when I enter the next set
of data and hit the transfer macro, it just writes over
the first set of data.

How can I get it to skip a line and enter a new line of
data? After I transfer all the data I copy it into a
Word document that has our labs header.

TIA
Steve

This is not a critique, I just want to know
Bob, is there a reason why you use: cLastRow = Cells(Rows.Count,"A").End(xlUp).Ro
instead of: cLastRow = Cells.End(xlDown).Row
When they seem to return the same value
I notice you use this style in all your coding
Is there something inherently better about End(xlUp).

----- Bob Phillips wrote: ----

Steve

First calculate the last used row on the second shee

cLastRow = Worksheet("Sheet2").Cells(Rows.Count,"A").End(xlUp ).Ro

then change the paste code to use that value +1. Foir instance, if your cod
looks lik

Ramge("A1:A5").Copy Destination:=Worksheets("Sheet2").Range("A1"

change this t

Ramge("A1:A5").Copy Destination:=Worksheets("Sheet2").Range("A"
cLastRow+1

--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct

"Steve Schultz" wrote in messag
..
I am a metrologist and use an Excel spreadsheet to ente
six data items that calculate various weight corrections
air density, etc. I then need to transfer 5 data item
from that spreadsheet to another sheet in the sam
workbook aligned in a row. After that, I go back to th
main spreadsheet and enter new data for the next weight
test and do the same thing, except that the new set o
data should be in the row immediately underneath th
previous row
I have recorded a macro which does that, using copy, the
paste, for the 5 items. However when I enter the next se
of data and hit the transfer macro, it just writes ove
the first set of data
How can I get it to skip a line and enter a new line o
data? After I transfer all the data I copy it into
Word document that has our labs header
TI
Stev

Chris,

Rather than just tell you the answer, try this example, and you should see
why.

Fill cells in column A like so

A1:1
A2:2
A10:3

note, nothing in A3:A9

Then run the 2 pieces of code, and see what you get.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"chris" wrote in message
...
This is not a critique, I just want to know.
Bob, is there a reason why you use: cLastRow =
Cells(Rows.Count,"A").End(xlUp).Row
instead of: cLastRow = Cells.End(xlDown).Row
When they seem to return the same value.
I notice you use this style in all your coding.
Is there something inherently better about End(xlUp).

----- Bob Phillips wrote: -----

Steve,

First calculate the last used row on the second sheet

cLastRow =
Worksheet("Sheet2").Cells(Rows.Count,"A").End(xlUp ).Row

then change the paste code to use that value +1. Foir instance, if
your code
looks like

Ramge("A1:A5").Copy Destination:=Worksheets("Sheet2").Range("A1")

change this to

Ramge("A1:A5").Copy Destination:=Worksheets("Sheet2").Range("A" &
cLastRow+1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steve Schultz" wrote in message
...
I am a metrologist and use an Excel spreadsheet to enter
six data items that calculate various weight corrections,
air density, etc. I then need to transfer 5 data items
from that spreadsheet to another sheet in the same
workbook aligned in a row. After that, I go back to the
main spreadsheet and enter new data for the next weight I
test and do the same thing, except that the new set of
data should be in the row immediately underneath the
previous row.
I have recorded a macro which does that, using copy, then
paste, for the 5 items. However when I enter the next set
of data and hit the transfer macro, it just writes over
the first set of data.
How can I get it to skip a line and enter a new line of
data? After I transfer all the data I copy it into a
Word document that has our labs header.
TIA
Steve

Bob
Thanks loads! It works just fine, except the very first entry gets
entered twice. I can just delete that line before I cut and paste to
Word.

Thanks again,
Steve


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!