How to transfer data from one sheet to another without cut & paste method?

[jkumar8877]

Hi
I have two sheets in attached workbook "Problem sheet". Sheet 1 is "Sem4" and sheet is "results". Sem4 contains roll number & name of students with their internal and external marks scored in particular subject of study (currently int & ext columns are left blank). The sheet "results" contain marks obtained by those students in their respective subjects.
I want to transfer the marks of each student from sheet "result" to sheet "sem 4" in their respective columns & rows without using copy/paste method. Instead I would like to know whether there is any other way of transfer the data from "result" sheet to "sem 4" sheet either using VB codes/userforms or using some formulas. Doing a transpose method, then copy pasting them is very long & tedious way.

Can some one help me with suggestion/solution in this regard?

Thanks
Jijesh

[hectop]

It can be done with VBA code. The simplest way, I think, is shown below.
You can make it more dynamic by finding/setting variable names for the columns in Sem 4 and rows/columns in Result that you are working with.
You could also display error messages for students with no results.

Please Login or Register  to view this content.

[protonLeah]

Maybe a template with a copy button:

[jkumar8877]

Hi Hectop
Thank you very much for your valuable suggestion. It seems to work, but since I am not having programming knowledge in visual basic or any other language i am don't understand the entire bit of coding that you have designed in this thread. Would you be able to send me video file explaining it?

Thanks
Jijesh

[jkumar8877]

Hi ProtonLeah
I think you didnot get me. I want to transfer marks of each student from the "result" sheet into the "S4" sheet without copy& paste function. Instead, I use some mathematical/logical formulas or any using program codes like in visual basic to do the entire job in just one or two mouse click. To let you know the data in the sample attached is of few students only. I have around 65 students in my list with all their mark sheet in one sheet. Hope you can now tap your forehead and get a better solution soon. Looking forward to your intelligent feedback and help.

Thanks
Jijesh

[hectop]

No, I won't get into video files. If you have specific questions, please ask.

To start open the sheet you want to have the macro located in.
You'll need the Develop command on the tool bar
Click on Developer | Visual Basic to open the VBA editor window
Click on Insert | Module
You can then copy the code into the module.
To run the macro you could click on Developer | Macros | select Consolidate then click on Run

The code as it sits only deals with the first two subjects. You would need to add the code for the remaining.
The changes would consist of

Please Login or Register  to view this content.

You need to change Col_offset to reflect the column number where you want the mark placed.
You need to change Row_offset to reflect the row from which you want to pull the mark.