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
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

615 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