Solved

How to create a Macro in Excel 2003 to take values from one sheet to the next sheet

Posted on 2011-02-15
16
272 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:jimjoseph
  • 8
  • 7
16 Comments
 
LVL 16

Expert Comment

by:Kalpesh Chhatrala
ID: 34896059
0
 
LVL 1

Author Comment

by:jimjoseph
ID: 34896188
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.
0
 
LVL 5

Expert Comment

by:wellous
ID: 34899622
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
0
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 
LVL 5

Expert Comment

by:wellous
ID: 34899640
Sorry , forgot the file ;-)
modified-JER-NEW-FORMAT.xls
0
 
LVL 1

Author Comment

by:jimjoseph
ID: 34906608
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?
0
 
LVL 5

Accepted Solution

by:
wellous earned 500 total points
ID: 34932738
Hi jimjoseph,
Thank you for your encourging words , really appreciated !
It's very easy to avoid this from happening , the easiest way is to select the whole row then delete it :) .. see very easy ..
OR
A little bit of complicating the current Lookup is to add IF before it like how i made in yr sheet.

Cheers,
Wellous


modified-JER-NEW-FORMAT.xls
0
 
LVL 1

Author Comment

by:jimjoseph
ID: 34942513
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?
0
 
LVL 5

Expert Comment

by:wellous
ID: 34950845
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


0
 
LVL 1

Author Comment

by:jimjoseph
ID: 34951379
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?
0
 
LVL 5

Expert Comment

by:wellous
ID: 34951972
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
0
 
LVL 1

Author Closing Comment

by:jimjoseph
ID: 34958598
Thanks Walid. You have been immensly helpful.
0
 
LVL 5

Expert Comment

by:wellous
ID: 34961029
Thank you Jim.
Wish you the best,
Walid
0
 
LVL 1

Author Comment

by:jimjoseph
ID: 35464962
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
0
 
LVL 5

Expert Comment

by:wellous
ID: 35497770
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
0
 
LVL 1

Author Comment

by:jimjoseph
ID: 35500266
Super! I would have doubled the points if I could.
0
 
LVL 5

Expert Comment

by:wellous
ID: 35500638
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question