hello
I have userform when I fill data then copy to sheet "invoice" what I would to be coping data to sheet "data" this is like archive invoices , so I put expected result in sheet data and every invoice is filling in sheet " invoice" should copy to bottom the old data of invoice has already copy earlier
It's not that hard to do, I'll download the file and let you know as soon as I can work it out
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
Once the invoice is filled you can copy the information to the Data sheet.
Delete the merged cells you have in the data sheet.
You can format the columns in the Data sheet accordingly.
Please Login or Register to view this content.
hi, @keepellah take your time
hi @ daves actually I tested your code but it gives me error about merged cells in this line
but I think cancelled all of merged cells , may you check my updating file ,please?HTML Code:
davesexecl code has a problem.
Thre range you copy is not alwa\ssy B16 to G30 you need to find the last filled row
daveexcel, the last row should be calculated but the problem is the last row in data is a merged cell.
I hate people using merged cells, but that can be fixed.
delete the last two rows in DATA and rebuild them after copying but NO merging would be my idea.
Apart from that the code the OP has posted is sloppy.
Needs tailoring but your macro works if you delete the last two data rows, also you need to format the data after copying that's not done either.
Hope the OP learns from this too, that's the goal, help others build better macros
@ keepellah you're right even if I cancels emerged cells there is a problem not show value of invoice totalalso you need to format the data after copying that's not done either.
you can see the result in attached file
If the other cells have no data, what difference does that make? the code finds the last filled cell in the data sheet to insert the other information.Originally Posted by Keebellah
Did you not see in my post that merged cells need to be deleted in the data sheet?
Just work on your own stuff.
Purely an observation, normally an invoice data recording system would not normally store the final summations just the data that is necessary to perform that function.
This ensures that simple table format is easier to configure and use.
torachan.
Someone has long toes here, sorry to have pointed it outYou work on your own stuff.
I’ll leave this to mr Dave, he knows what he’s doing
thanks @ keebellah for your sharing ,I appreciate your assistance , advice and opinion
so I have to wait maybe any member helps me
This is my modification on Mr D's code.
You must add a check to the command button to verify that all fields in a row are completed before saving.
The subtotal is set in a formula after copying that data
Might need fine-tuning but this works so far
I also ordened your tab order in the userform, so when you press tab it goes to the next field and not up and down like it was
"Work on your own stuff" , was supposed to mean supply your own answer.
You were not pointing anything out that I did not take into consideration but it is nice that you thought you needed to speak for the OP.
it's okay, let's just hope the OP can use the ideas he's gotten; that's the main goal here anyway
@ keepellah I apprecite your mod the code and trying to solve my problem , but there is missing I would show the total row is each invoice alone not together and breaking among invoices not sucking you can see what I mean in attached file
Last edited by ABDELFATTA; 01-14-2021 at 02:33 AM.
You should explain things more clearly when asking.
I'm sure that can be doen. will look into it.
I still advice you to add code to check if the invoice is complete before you press the command button, else the data will not copy correctly and you will have to add many exceptions when copying
The caption on your button says Add or Update Invoice, the code by Dave only adds, not updates.
Will this be your next question?
Refer to post# 11.
you are developing a data storage nightmare
torachan.
@torachan: with that I agree
You idea is indeed a nightmare.
I changed the DATA table to an actual table with a subtotal row.
This link will help you understand tables (Listobjects) https://www.thespreadsheetguru.com/b...0with%20Tables
The file now adds the data to this table and the subtotal is the bottom row.
If you filter the invoice or customer the total is according to what you see.
If you don't want it this way, well, you've got enough code to practice with and make the necessary changes
Happy coding
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks