copy entire data immediately when entered Solved/Closed

Question

Hello, 

I have a spreadsheet in which I maintain sale records of a no. of polymers. For every polymer (For eg. Adflex Q 100 F), I have a table showing Sr. No., Sale Date, Month, Customer Name, Customer Contact & Sell Qty.
I have 50-60 polymer grades, so it gives 50-60 tables for each. I enter the sell detail on day-to-day basis in respective grade table.
Now, what I want is to create a database. As soon the the data is entered in any of the tables (in sheet named as APO), it should get copied (along with the grade name) to the next sheet (which I call Database) in the same workbook.

I am a very beginner in Excel. Please help me out with this.

Configuration: Windows Vista / Firefox 7.0.1

TrowaD · Answer

Hi Chaaru,

Is it possible to upload (www.speedyshare.com) your workbook, without any personal info, so I know how your sheets are made up?

Best regards,
Trowa

Chaaru · Answer

Hi Trowa,
the link is
http://speedy.sh/r5MCQ/Polymer-Workbook-rough.xls

In the file, I want the sheet to copy data from sheets APO, PB, PE & PP to Database. The earlier 4 sheets (APO to PP) have tables in individual polymer's name. So say, if I am entering a fresh sale record in table of "Adflex Q 300 F", the information in columns from PO Date to Qty should get duplicated in the last sheet which I call "Database". So whenever I am entering a fresh sale record (which I have to on day to day basis), it should get copied in the next working row of Database sheet.
Is there any way to do this ? I also want the grade name to reflect in the respective sale record in Database sheet. Please help with this.

TrowaD · Answer

Hi Chaaru,

Here is your file: http://speedy.sh/QFRBx/Chaaru-Polymer-Workbook-rough.xls

Notice that on sheets APO cell A1 the contents are split over two cells to separate the polymer's name. You should do this for all polymer names for the code to work in the right way.

Now select a cell (doesn't matter which one) of the row you just entered a new entry and run the code.

Note that you can add a short-cut key to the macro. I did this for you and gave it the short-cut Ctrl+t. So select a cell of the right row and hit Ctrl+t to copy the data to the database sheet.

Let me know if more assistance is needed.

Best regards,
Trowa

Chaaru · Answer

Hi Trowa,
Thank you soo much for the code. I changed the grade name as asked by you. But when I entered the data in table Lucalen A2540D of sheet "PE" and ran the short cut by pressing "Ctrl+t" (cell A16 being selected), the row didnt appear on the Database.
Secondly, can every new row have a subsequent no. (starting from 1) whenever data is copied in the "Database"?
Also, what will happen if I increase the no. of rows of any of the table or tables in sheets 2 to 4, will that affect the code ?
Please let me know.

http://speedy.sh/XYMuJ/Chaaru-Polymer-Workbook-rough.xls

TrowaD · Answer

Hi Chaaru,

1. Forgot that Ctrl+t would show formula's in cells, thus not activating the code. I changed it to Ctrl+Shift+t. You can change the short-cut yourself if you want by going to top menu's > Extra > Macro > Macro's... > Options.

2. Added a new column to the Database sheet called Row No. which will count the entries.

3. You can add as many rows as you want to the tables as long as you don't have empty rows between entries. You can do the following to imitate a part of the code:
Goto column B of the selected row (i.e. B16).
Hit the End and then Arrow up.
Hit End and Arrow up again and you should now have selected the cell with the polymer's name.

If you repeat the above only now with empty row(s) between entries, you will notice that you don't end up with the polymer's name. Thus the code will not work as foreseen.


I don't know what situations might occur, so fool around and see if the code is still performing as planned.
Post back your findings/questions so we can tweek the code some more.

http://speedy.sh/XYRsJ/Chaaru-Polymer-Workbook-rough.xls

Best Regards,
Trowa

Chaaru · Answer

Hi Trowa,
Sorry to reply you soo late. I was out of town for few days.
The excel sheet you have sent is working absolutely fine.
Thanks for the help.. :)

-Chaaru

Copy entire data immediately when entered

6 responses

Similar discussions

Subscribe To Our Newsletter!