Copy data from a sheet to the next row in a different sheet

[T1m]

Hi All,

My name is Tim and I am a total newbie at excel so I am sorry if my question sounds dumb. I normally repair PC's and Laptops but am trying to help a friend with his invoicing and have found I am enjoying working with excel to the point where I am now stuck !

I have made an invoice, managed to work out how to do lists so he can alter his VAT etc and I wanted to progress further by having a couple of buttons that he could press to:- 1. Add a new customer to his database and 2. Recall an existing customer so that he didnt have to enter customer details if they were already in the database.

Sooooo.

I'm not worried about part 2 just yet as it's well beyond me at the moment but I am so close to getting part 1 working - I just need a little help with adding the customer data to the next row in the database sheet !

Basically, sheet 1 is the invoice, sheet 2 is the list sheet for VAT, and sheet 3 will be the database. I set up a button on sheet 1 that calls a macro to copy each individual cell with customer data into a SPECIFIC cell in sheet 3 - I was really pleased with myself when it worked - I even managed to google and find out how to disable the screen from updating so that it didn't "flash" during the copy and was a lot faster !
Buuuut, I am now stuck ! I can see that I need a command like Dim lastrow=xxx and I tried various lastrow finding macros but for the life of me I cant workout how to modify my copying macro to work with the lastrow Dim.

Here's a portion of the macro I use to copy from invoice sheet to database sheet - Again, I apologise if it looks daft but it works (allbeit for one set row!) and I have no knowledge of excel to speak of. I repeat the same code changing the destination field to B4, C4, D4 etc (4 is the last row at moment and is the bit I need to be automated)

' Customer Surname Name
Range("F14").Select
Selection.Copy
Sheets("Lookup Table").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Invoice").Select
' Customer Address
Range("D15").Select
Selection.Copy
Sheets("Lookup Table").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Invoice").Select

If someone could point me in the right direction I'd be grateful - half the problem I am having is that I don't know the technical terms used in excel so it's hard to put into google what I want to do to get a result back.

Kind Regards,

Tim.

 

[AlphaFrog]

Try something like this...

    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim Nextrow As Long
    
    Set wsSource = Sheets("Invoice")
    Set wsDest = Sheets("Lookup Table")
    
    ' Next empty row on the destination worksheet
    Nextrow = wsDest.Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1
    
    ' Customer Surname Name
    wsDest.Range("C" & Nextrow).Value = wsSource.Range("F14").Value
    ' Customer Address
    wsDest.Range("D" & Nextrow).Value = wsSource.Range("D15").Value

There are different methods in VBA to find the last used row on a sheet each with their own Pros\Cons. Here's a good site that describes them.
Excel Ranges: Finding the Last Cell

 

[T1m]

THANKYOU very much Alphafrog

I was trying to do it by

Range("D"+LastRow+1).Select !!!

I knew I was close, just that '&' that makes all the difference !

Have a great weekend and thanks again for your time.

Kindest Regards,

Tim.

 

[AlphaFrog]

You're welcome.

Tip: try to avoid using .Select and Selection. It's usually not necessary and it's slow. I know that's how the macro recorder does it but it's job is to record all your mouse clicks. VBA doesn't actually require a cell or sheet to be Selected to do something with it.

 

[T1m]

Noted.

As I said before, I really do not know anything about visual basic. I was asked to modify his existing form so he could add VAT to it after he became VAT registered, but it was from an office 97 wizard by village software and all the macros were passworded. So, I started from ground up and made him a new invoice. I enjoyed working with excel so much that I stated to look at the invoice and thought to myself, what a pain in the a$$ having to enter customer information time and again - especially when half of his customers are regulars and so I decided to have a try at writing the stuff myself. I was really pleased to have got it to the stage where I could press the macro button and it copied the data to a new worksheet but equally annoyed because I couldn't workout how to make the lastrow thing work.

Also annoying is the fact that I can understand a fair bit of the code as I used to program in Basic when I was a kid some 35 yeas ago hehehe. There are many similarities and although it's been a while for me it's a bit like riding a bike I guess, once you learned, you never forget !

I think I will be a regular visitor to this forum now mind, as computers have progressed a lot since my old commodore vic20 days !!!

I had a rethink about this invoice thing tho, and I think it's going to be better in the long-run if I separate the customer data into a new excel sheet and just update that one, as the guy that uses the invoice saves a copy to each of his customers folders currently and if he saves the database each time it's going to eat up his hard drive space.

Eventually, If I can learn more, then I am hoping that the whole invoice/work done to vehicle records etc can be saved to the database and recalled if needed........

Anyhow, I'm off to hunt for info on how to bring back the saved data and present a list of "matching customers" for selection so that the relevant customer information can be automatically added into the invoice without having to type it in......... Oh, and I need to work out how to check that the customer information is not already in the database before it gets saved as well !

Plenty to keep me busy with this little project, and I'm sure it won't be long before you see a new post from me hehehe. Appreciate the tips and the links - It really helps me to see alternative/correct ways of doing things.

Regards,

Tim.

 

[AlphaFrog]

My best friend had a Vic20. Two weeks of programming and compiling on a cassette tape drive and we got it to play Mary had a little lamb.

Suggestions for your project.

1.) Have the blank invoice form and customer database all in one workbook. Have a Save Invoice button that saves only a copy of the filled in invoice wokshsheet without the customer database. You could also keep a log of the saved invoices in a 3rd sheet.

2.) Use the .Find method to lookup customers in the database