In attached file is for hotel receipt it is based on number of nights the guest stay. If the guest stay 1 night all data must me filled automatically via vba and if the guest stay 2,3,4, 5, or six nights it must be done automatic via vba and last massage row shoud move down as data fills based on # of nights the guest stay. I need help.
Attached is my file i need a help with vba
- Thread starter urvashi1
- Start date
When I fill the data in my user form like name arrival date departure date rate etc that will fill the receipts in my sample based on the # of nights that will automatic I have formulas in cells I wanted to use vba to fills those cells if guest stay one night it will fills the data for one night and if for two nights it will fills the data in next cells like that it should fill based on # of nights and last cell that has massage has to move down via vba.
Here i really wanted to do. I wanted to create a user form like this this
First name___________
Last Name___________
Address_____________
City____________
State____________
Zip_____________
Rate____________
Check in____________
Check Out_____________
Payment_________
Room Type___________
Ref folio____________
Conf #___________
Guest Bill #____________
Clerk__________
# of nights_________
Total__________
This is my user form, now when i fill in this user from it should populate the data
like this based on the # o night guest stay
A2 B2 c2 d2 e 2 f2 g2 h2
Motel7
2420 Commerce Rd.
Goodland, KS 233456
USA (CellG3) .Sunny Patel
(G4)1234 Sample st.
(g5) Sample town, CO 60604
This should populte from user form)
(A10)Guest Bill # _____ of the DD/MM/YYY(This check out Date)
(A11) Stay of Sunny Patel (Sunny patel will comes from user Form)
(A12) From MM/DD/YYYY to MM/DD/YYYY ( Date will come from Chk in and out
date from user form)
(A13)
Ref:Folio ID- 123456:Conf No.- 43831889636:Guest No.- 18731:Clerk- SPP ( 15/03/15)
(ref foilio will comes from user form, confirma comes from user form and guest no and clerk id with check in date comes from user form)
Date department Rm Qty Chrage Total
03/14/15 Cash 0 0 0.00 (44.39)
03/14/15 NQ [03/14] 216 1 39.99 39.99
03/14/15 City Tax [03/14] 216 1 0.19 0.19
03/14/15 County Tax [03/14] 216 1 0.71 0.71
03/14/15 State Tax [03/14] 216 1 2.52 2.52
03/14/15 Other Tax [03/14] 216 1 0.98 0.98
Net 39.99
Total Tax 4.40
Total Charges 44.39
Total Credit 44.39
Balance 0.00
this will populate from user from when guest will stay one night, if guest stay 2 nd ,3 rd or fourth night it should populate below this by date Tax rate for the city and state other tax are formulas based on the rate. in total column (44.39) is the payment made by the guest. On the total tax, charges , credits and balance should move down based on the # of night guest stay.
I wanted to do this via macro and user form. i know how to create the user form, but i need help to create the macro. Thank you. I have uploaded the sample file for ref.
First name___________
Last Name___________
Address_____________
City____________
State____________
Zip_____________
Rate____________
Check in____________
Check Out_____________
Payment_________
Room Type___________
Ref folio____________
Conf #___________
Guest Bill #____________
Clerk__________
# of nights_________
Total__________
This is my user form, now when i fill in this user from it should populate the data
like this based on the # o night guest stay
A2 B2 c2 d2 e 2 f2 g2 h2
Motel7
2420 Commerce Rd.
Goodland, KS 233456
USA (CellG3) .Sunny Patel
(G4)1234 Sample st.
(g5) Sample town, CO 60604
This should populte from user form)
(A10)Guest Bill # _____ of the DD/MM/YYY(This check out Date)
(A11) Stay of Sunny Patel (Sunny patel will comes from user Form)
(A12) From MM/DD/YYYY to MM/DD/YYYY ( Date will come from Chk in and out
date from user form)
(A13)
Ref:Folio ID- 123456:Conf No.- 43831889636:Guest No.- 18731:Clerk- SPP ( 15/03/15)
(ref foilio will comes from user form, confirma comes from user form and guest no and clerk id with check in date comes from user form)
Date department Rm Qty Chrage Total
03/14/15 Cash 0 0 0.00 (44.39)
03/14/15 NQ [03/14] 216 1 39.99 39.99
03/14/15 City Tax [03/14] 216 1 0.19 0.19
03/14/15 County Tax [03/14] 216 1 0.71 0.71
03/14/15 State Tax [03/14] 216 1 2.52 2.52
03/14/15 Other Tax [03/14] 216 1 0.98 0.98
Net 39.99
Total Tax 4.40
Total Charges 44.39
Total Credit 44.39
Balance 0.00
this will populate from user from when guest will stay one night, if guest stay 2 nd ,3 rd or fourth night it should populate below this by date Tax rate for the city and state other tax are formulas based on the rate. in total column (44.39) is the payment made by the guest. On the total tax, charges , credits and balance should move down based on the # of night guest stay.
I wanted to do this via macro and user form. i know how to create the user form, but i need help to create the macro. Thank you. I have uploaded the sample file for ref.
Hi:
I know Simayan is working on your file. Here is what I have come up with.
I am not sure about your methodology of receipting a single customer on a day-to-day basis. why can't you provide the customer with a single consolidated bill instead of issuing multiple bills? Is there any business requirement to issue bills on a daily basis , if not some small tweaking in the formulas will give you what you are looking for and you may not be even needing a userform or any complex macros to get the work done.
Thanks
I know Simayan is working on your file. Here is what I have come up with.
I am not sure about your methodology of receipting a single customer on a day-to-day basis. why can't you provide the customer with a single consolidated bill instead of issuing multiple bills? Is there any business requirement to issue bills on a daily basis , if not some small tweaking in the formulas will give you what you are looking for and you may not be even needing a userform or any complex macros to get the work done.
Thanks
Hi Nebu,
I agree on your point, For the same I have changed my design where total tariff is calculated based on rate * No of nights.
Hi Urvashi,
In the attached file I have provided two macros
1. will save records in a database
2. another will generate last record of the database in a separate sheet, renames the sheet & make ready to be print in proper format.
@ Nebu : it would be gr8 if you can once go through it to vet it.
I agree on your point, For the same I have changed my design where total tariff is calculated based on rate * No of nights.
Hi Urvashi,
In the attached file I have provided two macros
1. will save records in a database
2. another will generate last record of the database in a separate sheet, renames the sheet & make ready to be print in proper format.
@ Nebu : it would be gr8 if you can once go through it to vet it.
Hi Simayan:
I have cleaned up your code to generate bill a bit. There were lots of lines to select a particular cell or a range while copying and pasting. You don't have to select a cell or a range while copying or pasting. You can go through your code and mine and see the difference.
Thanks
I have cleaned up your code to generate bill a bit. There were lots of lines to select a particular cell or a range while copying and pasting. You don't have to select a cell or a range while copying or pasting. You can go through your code and mine and see the difference.
Thanks