VBA to copy to another worksheet based on a matching cell value.

[Palacemad]

Hi.

Complete newbie here - literally just joined! Also pretty new to VBA and so really finding my way around it all, but I'm very eager to learn and understand a lot more.

I'm currently producing some spreadsheets for someone else that I need to automate as much as possible using VBA. I have figured out several issues so far but my current challenge has really got me stuck. I will try to explain this as simply as possible:

One worksheet - cohort summary is to contain a summary of data from a range of individuals. The name of the individual sits in Cell B8, when a new individual is added to the workbook, their name is added in the cell below. Each individual has their own individual worksheet (named after them - the VBA for this is already set up and works). The persons name sits in cell D3 on their individual worksheet. The individual worksheet has a set of data and will need to go to the following cells in the sheet "Cohort Summary":

Origin Cells Destination Cells
Q9,S9,U9,W9 Column C,D,E,F,G
(The missing columns are hidden,
the data in them is used to present
data in a chart).

Q21,S21,U21,W21 Column H,I,J,K,L
(Again, there are hidden cells)

Q33,S33,U33,W33 Column M,N,O,P,Q
(Hidden cells again)

Z18:AD18 Column R,S,T,U,V
(No hidden cells needed)

The required row will depend on the name matching from cell D3 from the individual worksheet (Which will be the active worksheet) matching the same name in the B column in the cohort summary. I need to identify the source worksheet as the active worksheet as the sheet does not get created until an individual is added to the workbook (There is another sheet in the workbook where the person's details are entered, creating their own unique individual worksheet).

I hope that makes sense and appreciate any help that can be given. If screen shots of the workbook would help make things easier I am happy to add them.

Thank you again in advance, any ideas will be greatly appreciated.

 

[DanteAmor]

Welcome to the forum.

I did not understand very well.
In cell D3 do you have the name of the sheet?
The destination sheet already exists?
Do you want to pass data from the active sheet to the destination sheet?

 

[Palacemad]

Hi.

Thanks for the reply.

In cell d3 is the name of the active sheet. It also holds the name of the individual who the data needs to match to in the destination sheet. I will want the data from the active sheet transferring to the destination sheet.

Hope that helps clear it up a little.

 

[DanteAmor]

Hi.

Thanks for the reply.

In cell d3 is the name of the active sheet. It also holds the name of the individual who the data needs to match to in the destination sheet. I will want the data from the active sheet transferring to the destination sheet.

Hope that helps clear it up a little.

But if in D3 is the name of the active sheet, how do I get the name of the target sheet?

 

[Palacemad]

The name of the target sheet is “cohort summary”. I’ll add some screen shots shortly!

 

[DanteAmor]

I'm sorry, I'm already lost. From which sheet to which sheet you must pass data. Where do I get the names of the sheets that will be used?

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

But do not just upload the file, try to explain with a couple of examples what you need.

 

[Palacemad]

HI. Here are some screenshots, if this doesn't help I will add the file to dropbox to share.

The work is for a school - when a new pupil is added to the worksheet the above page is created for them. The name of the sheet is automatically named after the name of the child. I want to transfer the information that you can see in the tables on the right hand side (the data will be filled in progressively through the year) in to the following worksheet:

Additionally, when a new pupil is added to the worksheet their name is also added to the above sheet. I want the data adding from the previous sheet to this one. It will be filled in the row across the page and will need to match up to the name of the correct pupil.

Does that help? I do appreciate your assistance.

 

[DanteAmor]

The screenshots are not visible. You have to upload the screens to dropbox.
But I need you to explain an example with information.

Additional. Do you have a macro that creates the sheets automatically?
Can you put the code?

 

[Palacemad]

Hi.

Here is a link to the file.

https://www.dropbox.com/s/qh5uo91ey8tufxs/Example.xlsm?dl=0


When you open teh file you will see 5 sheets:

1 - New Pupil Info - This is where new pupils are input in to the system. When the a new pupil is entered duplicate of the "Master" template sheet is reproduced but enters the correct details on for that particular child.
2 - John Smith - This is an example of a child I have created. On the second page of the worksheet I have entered examples of the data that I want entering on to "Cohort Summary" sheet.
3 - Cohort Summary. When a new pupil is entered their name is also entered in to this sheet.
4 - Master - The original template that each individual child's records are created from. This is where the macro code will have to originate from as this is the sheet that gets duplicated.
5 - Drop Down Options - Just where I have currently stored all my options for any drop down menus that are used.

My task currently is to ensure that the data from the boxes on the right hand side of John Smiths sheet match up to his name on the "cohort Summary" sheet.

I hope that helps to make more sense of it.

 

[DanteAmor]

Sorry, but I do not understand.
You want to pass the data from the "John Smith" sheet to the "Cohort Summary" sheet or
You want to pass the data from the "Cohort Summary" sheet to the "John Smith" sheet
You can explain it with an example.