Hi. I am wanting to transfer data from one sheet to another within the same workbook. To put it in to context it is a spreadsheet for my work (school) for transferring data about pupil achievement in subjects.
I currently have a table (on a spreadsheet called Pivot Table) in the format for producing a a pivot table to allow me more effective data analysis. In column c (Starting in C8) are the pupils names, this is already pre populated when the pupil names are entered on to another page in the workbook. On a separate sheet called Reading I have the pupil names running vertically starting at C1 to AH 1 (this will vary depending on the number of children in the class). Further down the page there is the pupil outcome in that particular subject (in this case reading). So for example child A's name is in cell C1, child's A reading result in in cell C32, Child B's name is in D1, their reading result is in D32 etc.
What I need is for the reading outcome to be transferred to the Pivot Table sheet, but to now run vertically down column K. I need it to also match up to the correct name. I know I could simply write this as a simple copy and paste to the correct cells but this will take a long time, and I am also concerned that issues may be caused if a child leaves or a new one joins.
Using previously written coding I have so far come up with the following:
Sub DataToPivot()
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 = Sheets("Reading")
Set sh2 = Sheets("Pivot Table")
wName = sh1.Range("C1:AH1")
wCells = Array("C32:AH32")
wDest = Array("K:K")
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 "Data Transferred"
End Sub
This is producing a number of issues though for me, initially the wName and wCells is a problem (I think this is due to my incorrect use of the terms range and array but am not sure why. It also then produces an issue for me near the end of the code in the line beginning sh2.Cells(i, wDest...
I don't have a great deal of experience yet with VBA so any help will be gratefully appreciated, particularly if you can help explain where I am going wrong.
Thank you in advance!
I currently have a table (on a spreadsheet called Pivot Table) in the format for producing a a pivot table to allow me more effective data analysis. In column c (Starting in C8) are the pupils names, this is already pre populated when the pupil names are entered on to another page in the workbook. On a separate sheet called Reading I have the pupil names running vertically starting at C1 to AH 1 (this will vary depending on the number of children in the class). Further down the page there is the pupil outcome in that particular subject (in this case reading). So for example child A's name is in cell C1, child's A reading result in in cell C32, Child B's name is in D1, their reading result is in D32 etc.
What I need is for the reading outcome to be transferred to the Pivot Table sheet, but to now run vertically down column K. I need it to also match up to the correct name. I know I could simply write this as a simple copy and paste to the correct cells but this will take a long time, and I am also concerned that issues may be caused if a child leaves or a new one joins.
Using previously written coding I have so far come up with the following:
Sub DataToPivot()
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 = Sheets("Reading")
Set sh2 = Sheets("Pivot Table")
wName = sh1.Range("C1:AH1")
wCells = Array("C32:AH32")
wDest = Array("K:K")
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 "Data Transferred"
End Sub
This is producing a number of issues though for me, initially the wName and wCells is a problem (I think this is due to my incorrect use of the terms range and array but am not sure why. It also then produces an issue for me near the end of the code in the line beginning sh2.Cells(i, wDest...
I don't have a great deal of experience yet with VBA so any help will be gratefully appreciated, particularly if you can help explain where I am going wrong.
Thank you in advance!
