Hi all again,

This is the situation:

I have an invoice sheet that is printed out by the salesman. I woul
like to keep a record of details on the sheet whenever an invoice i
printed.

What i'm looking for is code for something that transfers the detail
in the cells to another worksheet but on a new line e.g:

Date Inv No Amount Salesman

22/07/05 1 £55.00 Bob
22/07/05 2 £10.50 Joan
22/07/05 2 £10.00 Joan
23/07/05 3 £20.00 Bart

All invoices are made on one worksheet and the sales info i
automatically deleted after each print.
All of the info will be on different cells scattered around the page.
Sorry if the question sounds a bit vague but i'm not sure how to g
about this.

Thanks for looking at the problem, i've always received great advic
here

--
chris10
-----------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...fo&userid=2516
View this thread: http://www.excelforum.com/showthread.php?threadid=38975

Chris,

lrw = Sheets("Sheet2").= Cells(Rows.COUNT, "A").End(xlUp).Offset(1, 0).Row

will give the first open row at the bottom of the second sheet
than just do something like
Sheet("Sheet2").Cells(lrw,1) = Sheets("Sheet1").Range("A1")

change the sheet names and A1 to what ever is appropriate
Cells(lrw,1) puts the data into a cell in column A

--
steveB

Remove "AYN" from email to respond
"chris100" wrote in
message ...

Hi all again,

This is the situation:

I have an invoice sheet that is printed out by the salesman. I would
like to keep a record of details on the sheet whenever an invoice is
printed.

What i'm looking for is code for something that transfers the details
in the cells to another worksheet but on a new line e.g:

Date Inv No Amount Salesman

22/07/05 1 £55.00 Bob
22/07/05 2 £10.50 Joan
22/07/05 2 £10.00 Joan
23/07/05 3 £20.00 Bart

All invoices are made on one worksheet and the sales info is
automatically deleted after each print.
All of the info will be on different cells scattered around the page.
Sorry if the question sounds a bit vague but i'm not sure how to go
about this.

Thanks for looking at the problem, i've always received great advice
here.


--
chris100
------------------------------------------------------------------------
chris100's Profile:
http://www.excelforum.com/member.php...o&userid=25166
View this thread: http://www.excelforum.com/showthread...hreadid=389755

Hi Steve,

Could you do me a big favour and give a simple example of the code yo
mentioned for say just sheet 1 and sheet 2. I'm not very experienced a
you know but an example would help me to play around a bit so i ca
piece together how it works.

Regards,

Chri

--
chris10
-----------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...fo&userid=2516
View this thread: http://www.excelforum.com/showthread.php?threadid=38975

Chris,

It helps if you use reply - that way the previous message(s) are kept in the
current message making it
easier to track what has been said before.

Let's say that the information is on Sheet1 (replace with the name of your
sheet)
Your data is ordered like this, with all the info on row 2

A B C D
Date Inv No Amount Salesman

23/07/05 3 £20.00 Bart

And Sheet2 is laid out in a similar way.

Than use this code before you clear the invoice sheet:
================================================== =======
Dim lrw as Long, cl as Long

' this gets the first open row on sheet2
lrw = Sheets("Sheet2").= Cells(Rows.COUNT, "A").End(xlUp).Offset(1, 0).Row

For cl = 1 to 4 ' cycle through the 4 columns, trans
each cell
Sheets("Sheet2").Cells(lrw,cl)=Sheets("Sheet1").Ce lls(2,cl)
Next
================================================

If the data is scattered:
Date in A5
Inv No in D25
Amount in T15
Salesman in C18

than use:
======================================
Dim lrw as Long, cl as Long

' this gets the first open row on sheet2
lrw = Sheets("Sheet2").= Cells(Rows.COUNT, "A").End(xlUp).Offset(1, 0).Row

Sheets("Sheet2").Cells(lrw,1)=Sheets("Sheet1").Ran ge("A5")
Sheets("Sheet2").Cells(lrw,2)=Sheets("Sheet1").Ran ge("D25")
Sheets("Sheet2").Cells(lrw,3)=Sheets("Sheet1").Ran ge("T15")
Sheets("Sheet2").Cells(lrw,4)=Sheets("Sheet1").Ran ge("C18")
============================================

Let me know if you need any further explanation or help....

Note that using Cells() is similar to using Range()
where Cells(r,c) = the range where row # = r and column position = c
(A=1,B=2,C=3,..............)

--
steveB

Remove "AYN" from email to respond
"chris100" wrote in
message ...

Hi Steve,

Could you do me a big favour and give a simple example of the code you
mentioned for say just sheet 1 and sheet 2. I'm not very experienced as
you know but an example would help me to play around a bit so i can
piece together how it works.

Regards,

Chris


--
chris100
------------------------------------------------------------------------
chris100's Profile:
http://www.excelforum.com/member.php...o&userid=25166
View this thread: http://www.excelforum.com/showthread...hreadid=389755

Hi SteveB,

When i try to run the below I get a Syntax Error for "Lrw=...." and i'm
not sure where the mistake might lie. I tried a few alterations but with
no luck. Please advise.

Chris

Sub bob()
Dim lrw As Long, cl As Long

' this gets the first open row on sheet2
lrw = Sheets("Sheet2").= Cells(Rows.COUNT, "A").End(xlUp).Offset(1,
0).Row

Sheets("Sheet2").Cells(lrw, 1) = Sheets("Sheet1").Range("A5")
Sheets("Sheet2").Cells(lrw, 2) = Sheets("Sheet1").Range("D25")
Sheets("Sheet2").Cells(lrw, 3) = Sheets("Sheet1").Range("T15")
Sheets("Sheet2").Cells(lrw, 4) = Sheets("Sheet1").Range("C18")

End Sub


--
chris100
------------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...o&userid=25166
View this thread: http://www.excelforum.com/showthread...hreadid=389755

There is a typo on this line:

lrw = Sheets("Sheet2").= Cells(Rows.COUNT, "A").End(xlUp).Offset(1, 0).Row

Change it to:

lrw = Sheets("Sheet2").Cells(Rows.COUNT, "A").End(xlUp).Offset(1, 0).Row

chris100 wrote:

Hi SteveB,

When i try to run the below I get a Syntax Error for "Lrw=...." and i'm
not sure where the mistake might lie. I tried a few alterations but with
no luck. Please advise.

Chris

Sub bob()
Dim lrw As Long, cl As Long

' this gets the first open row on sheet2
lrw = Sheets("Sheet2").= Cells(Rows.COUNT, "A").End(xlUp).Offset(1,
0).Row

Sheets("Sheet2").Cells(lrw, 1) = Sheets("Sheet1").Range("A5")
Sheets("Sheet2").Cells(lrw, 2) = Sheets("Sheet1").Range("D25")
Sheets("Sheet2").Cells(lrw, 3) = Sheets("Sheet1").Range("T15")
Sheets("Sheet2").Cells(lrw, 4) = Sheets("Sheet1").Range("C18")

End Sub

--
chris100
------------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...o&userid=25166
View this thread: http://www.excelforum.com/showthread...hreadid=389755

--

Dave Peterson

Dave,

Thank you for catching this!!!

My goof when I did a copy/paste... (and than didn't catch it...)

--
steveB

Remove "AYN" from email to respond
"Dave Peterson" wrote in message
...
There is a typo on this line:

lrw = Sheets("Sheet2").= Cells(Rows.COUNT, "A").End(xlUp).Offset(1, 0).Row

Change it to:

lrw = Sheets("Sheet2").Cells(Rows.COUNT, "A").End(xlUp).Offset(1, 0).Row

chris100 wrote:

Hi SteveB,

When i try to run the below I get a Syntax Error for "Lrw=...." and i'm
not sure where the mistake might lie. I tried a few alterations but with
no luck. Please advise.

Chris

Sub bob()
Dim lrw As Long, cl As Long

' this gets the first open row on sheet2
lrw = Sheets("Sheet2").= Cells(Rows.COUNT, "A").End(xlUp).Offset(1,
0).Row

Sheets("Sheet2").Cells(lrw, 1) = Sheets("Sheet1").Range("A5")
Sheets("Sheet2").Cells(lrw, 2) = Sheets("Sheet1").Range("D25")
Sheets("Sheet2").Cells(lrw, 3) = Sheets("Sheet1").Range("T15")
Sheets("Sheet2").Cells(lrw, 4) = Sheets("Sheet1").Range("C18")

End Sub

--
chris100
------------------------------------------------------------------------
chris100's Profile:
http://www.excelforum.com/member.php...o&userid=25166
View this thread:
http://www.excelforum.com/showthread...hreadid=389755

--

Dave Peterson

Thanks for the help guys. Tried it this morning and seems to work fine
I'll let you know how i get on later

--
chris10
-----------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...fo&userid=2516
View this thread: http://www.excelforum.com/showthread.php?threadid=38975

Just a quick thank you to those who helped with this one - added it thi
weekend and works superb. This little piece of code has just saved mayb
an 1hr of data entry a day. Thanks again

--
chris10
-----------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...fo&userid=2516
View this thread: http://www.excelforum.com/showthread.php?threadid=38975