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

 

[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

 

[chris100]

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

 

[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,..............)

 

[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

 

[STEVE BELL]

Dave,

Thank you for catching this!!!

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

 

[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 thi
weekend and works superb. This little piece of code has just saved mayb
an 1hr of data entry a day. Thanks again