jimjoseph
asked on
How to create a Macro in Excel 2003 to take values from one sheet to the next sheet
Dear Friends
I have attached an excel workbook with two sheets namely JER and CBPL. What I am hoping to get your help are:
1) A Macro button which when pressed pops up a box asking "Which Section do you require" and then " Please mention Section Code. (The section - A,B,C,D,E and Section code eg 1.0, 1.01, 1.02 etc are found on the second sheet CBPL)
2) Once we mention the code, the revelant description found in column C of CBPL(Sheet 2) gets automatically copied onto the D25 cell of JER(Sheet 1) and the relevant price in coloumn G of CBPL (Sheet 2) gets copied onto I25 cell of JER (Sheet1).
If we press the macro button again, and enter the section and code, the next description should fit in D26 and price should go into I26.
Is Macro the best way around for this? If so could you please help me with this?
JER-NEW-FORMAT.xls
I have attached an excel workbook with two sheets namely JER and CBPL. What I am hoping to get your help are:
1) A Macro button which when pressed pops up a box asking "Which Section do you require" and then " Please mention Section Code. (The section - A,B,C,D,E and Section code eg 1.0, 1.01, 1.02 etc are found on the second sheet CBPL)
2) Once we mention the code, the revelant description found in column C of CBPL(Sheet 2) gets automatically copied onto the D25 cell of JER(Sheet 1) and the relevant price in coloumn G of CBPL (Sheet 2) gets copied onto I25 cell of JER (Sheet1).
If we press the macro button again, and enter the section and code, the next description should fit in D26 and price should go into I26.
Is Macro the best way around for this? If so could you please help me with this?
JER-NEW-FORMAT.xls
ASKER
Hi Kalpesh
This just copies line by line to another sheet. I was looking for something more advanced in selecting and copying from one sheet to another sheet.
This just copies line by line to another sheet. I was looking for something more advanced in selecting and copying from one sheet to another sheet.
Hi jimjoseph,
Pls find the attachment , i modified a little bit your database and put it in same sheet (JER) starting row 100 ... ( down) after the print area ... also modified the codes to be (section with code) instead of having sections in column and codes in different column to simplify it...
I hope you like it.. it's not Macro it's LookUP condition..
Also i added another column in JER table called [Code ] with dropdown list to select the code you want then the discription and price will be filled automatically in Sale colum.
Good luck , Wellous
Pls find the attachment , i modified a little bit your database and put it in same sheet (JER) starting row 100 ... ( down) after the print area ... also modified the codes to be (section with code) instead of having sections in column and codes in different column to simplify it...
I hope you like it.. it's not Macro it's LookUP condition..
Also i added another column in JER table called [Code ] with dropdown list to select the code you want then the discription and price will be filled automatically in Sale colum.
Good luck , Wellous
Sorry , forgot the file ;-)
modified-JER-NEW-FORMAT.xls
modified-JER-NEW-FORMAT.xls
ASKER
Hi Wellous
This is absolutely absolutely amazing. You are a star. I did not think about V Lookup. I love it!
I do have a small query though. In the JER Sheet, if we do not enter the code, the column J shows #N/A and when the code is entered, the price automatically comes in correctly. However, I would want the SUM of the column J at the end and if I set the formula =SUM(J25:J60) it comes as #NAME because of the #NA. Do you know anyway I could avoid that?
This is absolutely absolutely amazing. You are a star. I did not think about V Lookup. I love it!
I do have a small query though. In the JER Sheet, if we do not enter the code, the column J shows #N/A and when the code is entered, the price automatically comes in correctly. However, I would want the SUM of the column J at the end and if I set the formula =SUM(J25:J60) it comes as #NAME because of the #NA. Do you know anyway I could avoid that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Wellous
Sorry for the delay as I was a bit unwell. I would have to go for the complicated IF function as this sheet is to be used by other and they might just enter proably the first three column and when the delete the rows the formula disappears. I however did something else, I added and Extra code which shows the price zero. So the default excel sheet contains the code for Zero price and as and when users enter codes the other rows remains as num and we can get a total.
I am not sure if I am streching your patience. I would require one more help. I am not sure of it is possible. There are some codes such as D 3.01 or E 4.0 which has price of 15%. Which means if we select D 3.01 in C25, then J 25 should be 15% of H 25. Can this be done on the same cell as it already contains a look up formula?
Sorry for the delay as I was a bit unwell. I would have to go for the complicated IF function as this sheet is to be used by other and they might just enter proably the first three column and when the delete the rows the formula disappears. I however did something else, I added and Extra code which shows the price zero. So the default excel sheet contains the code for Zero price and as and when users enter codes the other rows remains as num and we can get a total.
I am not sure if I am streching your patience. I would require one more help. I am not sure of it is possible. There are some codes such as D 3.01 or E 4.0 which has price of 15%. Which means if we select D 3.01 in C25, then J 25 should be 15% of H 25. Can this be done on the same cell as it already contains a look up formula?
Hi jimjoseph,
I suggest to place the actual price instead of 15% of the( Documentation cost+) ... just to simplify the formula ..
Remember that H25 column is empty , we didn't set any formula on it or even to look into it.
Brgds
Walid OMIRA
I suggest to place the actual price instead of 15% of the( Documentation cost+) ... just to simplify the formula ..
Remember that H25 column is empty , we didn't set any formula on it or even to look into it.
Brgds
Walid OMIRA
ASKER
Hi Walid
Sorry. I did not quite understand.J 25 has the lookup fromula. If we select D 3.01, then J25 = 15% of whatever the is amount going to be typed in H25. Are you saying that incase D 3.01 comes up, we have to manually calculate 15% and typeit in?
Sorry. I did not quite understand.J 25 has the lookup fromula. If we select D 3.01, then J25 = 15% of whatever the is amount going to be typed in H25. Are you saying that incase D 3.01 comes up, we have to manually calculate 15% and typeit in?
Hi Jimjoseph,
Yes , this is what i wanted to say... whenever D3.01 is set then calculate it manullay , to avoid complicating the formula with another IF , just to make it working.
Good luck.
Walid
Yes , this is what i wanted to say... whenever D3.01 is set then calculate it manullay , to avoid complicating the formula with another IF , just to make it working.
Good luck.
Walid
ASKER
Thanks Walid. You have been immensly helpful.
Thank you Jim.
Wish you the best,
Walid
Wish you the best,
Walid
ASKER
Hi Walid
I am so sorry for coming back late on this. While the users were happily working on the file, there is an apparent bug in the VLOOKUP. I am attaching the Page with the VLOOKUP you had created. Everything works fine till we use the drop down to select E9.0 and below the the value it is supposed to look up is incorrect. I recreated the table on another sheet and the problem persists. Could you kindly help?
JERRR.xls
I am so sorry for coming back late on this. While the users were happily working on the file, there is an apparent bug in the VLOOKUP. I am attaching the Page with the VLOOKUP you had created. Everything works fine till we use the drop down to select E9.0 and below the the value it is supposed to look up is incorrect. I recreated the table on another sheet and the problem persists. Could you kindly help?
JERRR.xls
Hi Jim,
No problem,
I corrected it,
Pls from now on if you want to put additional codes then select the source data and sort them according to the first row (A).
Good luck.
Walid,
JERRR.xls
No problem,
I corrected it,
Pls from now on if you want to put additional codes then select the source data and sort them according to the first row (A).
Good luck.
Walid,
JERRR.xls
ASKER
Super! I would have doubled the points if I could.
you welcome dear,
the question is closed already.. :)
Are you going to add more codes and details ( discriptions ) in the code source part in yr database?
if yes , then we can create new worksheet beside the existing one and mark it for lookup function.
pls tell me if you need any improvement ..
Good luck,
Walid
the question is closed already.. :)
Are you going to add more codes and details ( discriptions ) in the code source part in yr database?
if yes , then we can create new worksheet beside the existing one and mark it for lookup function.
pls tell me if you need any improvement ..
Good luck,
Walid
http://en.kioskea.net/faq/6033-excel-copy-data-from-one-sheet-to-another