Transfer macro

[Steve Schultz]

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

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)

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Steve Schultz]

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