How to get cell values from one sheet to an other

i All,

I'm afraid the first part of the question must be one of the most "noob" questions ever asked in this forum, but I just can not find how to do this.
It must be simple, but then again, that's all when you know it :-)

Here's my question:

I have made a "userform" to fill in data into cell's on the worksheet "Factuur"
The information is "Name", "Address", "Phone", "Email" etc. This works like a charm.

Next part, I have also a worksheet called "NAW" and the information from the fields on "Factuur" (e.g. : "Name", "Address", "Phone", "Email" etc.) should be copied in that sheet.
The first customer I put in, will have cell A2 till lets say G2.
The next customer will have A3 till G3, etc.

There are some tricky parts:
- Not all the fields are mandatory, so the data from the new customer needs to have a clean "row" (I can not use +1 code for the cells)
I have no idea how to fix this. When I try to copy the values, and one field is not filled in, my end result is dramatic.


- The customer needs to get a unique customer-number, This can be simple, like first customer customer ID 1, second 2, etc.
I know I need to do something with a +1, but I don't know how to do this.

Help is appreciated!

Hi vcoolio,

Yes, you are right! discrepancies.......
I don't know when this happened, but it did.
I'm sorry for this.
It's fixed in the next file (0.03 factuur DHZ macro.xlsm)

Can the "Kenteken" entry be used as the client ID? (I'm assuming that "Kenteken" refers to an ID number or such).

I'm afraid this is not an unique enough value. "Kenteken" = licence plate. A customer can have 2 cars, so 2 different licence plates.
While I'm typing this, it's gets confusing.
As I can not enter 2 different values in one cell, I guess the licence plate IS a good unique value.
If one customer has 3 car's, this customer then will be listed 3 times.
On the other hand, IF the car is sold, and the new owner also comes to us, we would have a problem.

I know, confusing
Best way is making column "A" an own unique ID.
This still does not solve the problem when a customer has 2 or 3 car's, But I guess there is a code possible to check if the Licence plate number exists, and if it does, to make a new row and a new customer ID.

As for the

"jumble" of reduced size rows

. I did not create the original document. Microsoft did. (they created the template for a bill, in the excel 2013, office templates section)

I also wonder why they started to play around with row sizes, there must be better way to get the end result.
Any way, we just used the template, and it was this way.

Natefarm already gave some code, thanks for that!

It's a push in the good direction.

As I cleaned up some coding, I truly hope the 0.03 version is more clear.

Hello again Fhorst,

Well, this may work in regards transferring data to the "NAW" sheet:-

Please Login or Register  to view this content.

You will need to go to the "NAW" sheet first and place these headings in row 1:-

Klant ID/Factuur #/Datum/Naam/Adres/Postcode/Plaats/Telefoon/E-mail

They need to be entered in the above order, as you see them. You can assign the macro to a button. Perhaps reduce the size of the two existing ones and insert a shape in between them and format it to suit yourself.

Now, I've entered Klant ID in cell G13 in the code above so you will need to create another title in cell F13 "Klant ID" in the invoice template. I tested it by manually entering a random number in cell G13 and it works just fine. The Klant ID are numbers you can create yourself. For example, start at 1001 and have them increment by 1 on clearing the contents of the invoice. No need to have them as part of your UserForm.

I entered the above code in Module 2 in your workbook to test it as the module is empty.

I hope this helps you.

Regards,
vcoolio.

Hello Fhorst,

Sorry to bother you again, but I just realised that we need to make a slight adjustment to the code.

In the second last line of code, change :-

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and add this:-

Please Login or Register  to view this content.

to the bottom of the code, just above End Sub.

The code in my previous post would have cleared the Klant ID number and the last line of code above will increment your Klant ID number by 1.

BTW, I am assuming that you like my idea of the Klant ID!!

Regards,
vcoolio

Hi Vcoolio,

Thank you for your input!
I do like the idea of "Klant ID", but not to show an extra field in the "Factuur" sheet.
So I made a small change:I made a cell text (number) the same color as the sheet, so you can not see it :-)
The rest stayed the same.

Thanks!!