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.

 

[Palacemad]

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.

Hi, yes I will want to pass data from the “John Smith” sheet to the “cohort summary” sheet.

As as an example...

A new child is entered into they database on the opening sheet. This then creates the child their own sheet named after them and enters their name on to the cohort summary” sheet. As the year progresses and different tests are done, teachers will input the child’s reading, maths, spelling ages in to the individuals sheet. I want this data to then copy across to the “cohort summary”.

i know how to get the data to copy across, its ensuring the data always finds the correct child in the “cohort summary”.

i hope that makes more sense.

 

[DanteAmor]

Please, do not leave the script.
You have a sheet called "John Smith" and you want to pass data to "Cohort Summary" sheet.
What data do you want to pass and where do you want to put it, but please, explain with examples.

eg.
On your "John Smith" sheet in cells Q9 you have the data "6.00", in which cell of the "Cohort Summary" sheet you want to put it.

Explain a couple of examples. Use the data you sent in the sheets to explain the examples.

 

[Palacemad]

I want to pass data from the "John Smith" sheet to the "Cohort Summary" sheet. The following are the cells on the "John Smith" sheet that I want to be copied and the cells on the "Cohort Summary" sheet that I want them to go to:

Origin Cells ("John Smith")
Q10,S10,U10,W10

Destination cells ("Cohort Summary")
C8,D8,E8,F8,

Origin Cells: ("John Smith")
Q21,S21,U21,W21

Destination Cells: ("Cohort Summary")
Column H8,I8,J8,K8,

Origin Cells: ("John Smith")
Q33,S33,U33,W33

Destination Cells: ("Cohort Summary")
Column M8,N8,O8,P8,

Origin Cells ("John Smith")
Z18:AD18

Destination Cells ("Cohort Summary")
Column R8,S8,T8,U8,V8

I understand how to copy these cells to the correct places using VBA, my problem is that I need to know how to ensure the data finds the correct row to be pasted on to. The requirement must be that the name of the row must match the name of the child that the data is been input. In the example I have posted the name John Smith can be found in cell "D3" (on the "John Smith" sheet) and can be matched up to cell B8 on the destination "Cohort Summary". However, as more pupils are added I need to ensure that the copied data matches up to the correct child's name.

 

[DanteAmor]

Execute the following macro on the pupil sheet.

Sub TransferDataToCohortSummary()
    Dim sh1 As Worksheet, sh2 As Worksheet, wName As String, cell As String
    Dim r As Range, f As Range, i As Long, j As Long, lr As Long, wCells As Variant, wDest As Variant
    
    Set sh1 = ActiveSheet
    Set sh2 = Sheets("Cohort Summary")
    
    wName = sh1.Range("D3")
    wCells = Array("Q10", "S10", "U10", "W10", "Q21", "S21", "U21", "W21", _
                   "Q33", "S33", "U33", "W33", "Z18", "AA18", "AB18", "AC18", "AD18")
    
    wDest = Array("C", "D", "E", "F", "H", "I", "J", "K", "M", "N", "O", "P", "R", "S", "T", "U", "V")
    
    Set r = sh2.Range("B:B")
    Set f = r.Find(wName, LookIn:=xlValues, lookat:=xlWhole)
    If Not f Is Nothing Then
        i = f.Row
        For j = 0 To UBound(wCells)
            sh2.Cells(i, wDest(j)).Value = sh1.Range(wCells(j)).Value
        Next
    End If
    MsgBox "transferred data"
End Sub

 

[Palacemad]

You sir, are an absolute gentleman.

Thank you greatly.

I aim to work myself through the code to understand what it is doing to hopefully help me in the future.

Thank you so much

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.