Paste after print to a new line

[chris100]

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.

[STEVE BELL]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[chris100]

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

[STEVE BELL]

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").Cells(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").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")
============================================

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[chris100]

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

[Dave Peterson]

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

[STEVE BELL]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[chris100]

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

[chris100]

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